ROUNDUP and ROUNDDOWN with a Twist

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