Help updating formula to leave "#ERROR!" result blank

[Sadie_Glisson]

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!

[kuovonne · Accepted solution]

Sadie_Glisson:

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

This support page says how to avoid ERROR!.

[Justin_Barrett]

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.

[Sadie_Glisson]

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