Skip to main content

Hi all!


I have a formula field that calculates a variety of numbers into an average and then translate them into star ratings, here:



REPT("⭐️", ROUND(AVERAGE(IF({Overall Review NUMBER not accepting 0 rating (from Reviews)}, {Overall Review NUMBER not accepting 0 rating (from Reviews)}, 0)), 0))


This works great, except when there is no star rating submitted, then the “number” is seen as NaN, and the formula spits out “#ERROR!”



Can anyone help update this formula to say: if result=“#ERROR!” then leave field blank ?



IF it really needs to be done in the number sequence calculation step, I’ll give you that info:



My formula for creating the number sequence in “Overall Review NUMBER not accepting 0 rating (from Reviews)” from above is this:



AVERAGE({Work/Life Balance}, {Pay & Benefits}, {Culture & Values})


I previously tried to turn NaN into 0, but then it factored in “0” into my average, which I don’t want because it brings the average down inappropriately. That code WAS:



ROUND(AVERAGE(IF({Pay & Benefits}, {Pay & Benefits}, 0), IF({Work/Life Balance}, {Work/Life Balance}, 0), IF({Culture & Values}, {Culture & Values}, 0)), 0)


Thanks in advance!



This support page says how to avoid ERROR!.

On a side note, the AVERAGE() function in your first formula—the one where you’re repeating the star emojis—is unnecessary. You’re averaging the values already in your other formula in {Overall Review NUMBER not accepting 0 rating (from Reviews)}. That field is only going to return a single value as the result of that its formula, and processing a single value with another AVERAGE() function is redundant.



This support page says how to avoid ERROR!.


Thanks! I was able to figure it out by requesting that empty ratings be left blank rather than errored.

Reply


Terms & Conditions