#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Rounding up the results of an IF formula

Options
edited 12/09/19

I finally figured out how to create an IF formula to get an AVG of Children divide that by a specific number, and enter "N/A" if there was nothing in the children cells.

NOW my boss would like to Round the resulting number up to the next dollar value. Sometimes, I think she just likes to test me! LOL

Here is the formula I have that works: =IFERROR (AVG(Children()) / 27, "N/A")

So, how do I take that result and make it round up to the next full dollar?

Any help is GREATLY appreciated

Annette

Tags:

• ✭✭✭✭✭✭
Options

See if this works for you. I think it covers your requirements:

=IF(IFERROR((AVG(CHILDREN()) / 27), "0") - IFERROR(ROUND(AVG(CHILDREN()) / 27), "0") < .5, IFERROR(ROUND(AVG(CHILDREN()) / 27) + 1, "N/A"), IFERROR(ROUND(AVG(CHILDREN()) / 27), "N/A"))

• Options

I come up with an #Unparseable error. Could it have to do with the "-" at the end of the first part of the IF?

I think I see where you're going with it. Nested IF statements aren't my forte but this should certainly help me learn more about it!

Thanks

• ✭✭✭✭✭✭
Options

did you put it at the parent row of the values column?

see the bold cell here...

• ✭✭✭✭✭✭
Options

Unless your errors are caused by something other than a divide by 0 (and maybe even then), this also works:

=IF(SUM(CHILDREN()) > 0, ROUND(AVG(CHILDREN()) / 27, 0), "N/A")

Craig

ps: I am curious about "27"

• Options

Hi Brett,

That worked very well except for the one's that should have the "N/A". Those come back as #INVALID OPERATION:-( So, close!!

Hi Craig,

Your formula worked like a dream! And the "27" is to convert our freight cost to board feet on our freight matrix for the 2 axle trucks:-)

Thank you both so much for your time and assistance! It is GREATLY appreciated!

Annette

This discussion has been closed.