Rounding up the results of an IF formula

Bobko

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

Brett Evans

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"))

Bobko

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

Brett Evans

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

 

see the bold cell here...

https://app.smartsheet.com/b/publish?EQBCT=5893d0261d8e4790bc944cbffad3f895

J. Craig Williams

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"

Bobko

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