Last week I had a question from Dan that I’d never come across before.
Dan has a dress shop with over 2000 dresses and he wanted a formula that would round prices up (in whole dollars) if it ended between 5 and 9, or down if it ended between 0 and 4.
For example: $143 would round down to $139, and $147 would round up to $149.
Now, the problem is that the ROUNDUP and ROUNDDOWN functions will do one or the other, so this is where ROUND is ideal.
=ROUND(147,-1)-1
=149
Why Minus 1 – Well, the -1 in
=ROUND(147,-1)
will give you $150, but we want $149 so we need to minus 1 from the rounded result.
ROUNDUP and ROUNDDOWN
If we always wanted to ROUNDUP or ROUNDDOWN then we could use these functions instead.
=ROUNDUP(147,-1)-1
=149
And
=ROUNDDOWN(147,-1)-1
=139