rounding help

[JM]

looking for the formula that will round up to the nearest nickel.

For example, have a price of $2.21 would round to $2.20 and $2.23 would
round to $2.25.

thanks

 

[Gord Dibben]

You want round up or round down.

=ROUND(A1/0.05,0)*0.05


Gord Dibben MS Excel MVP

 

[Guest]

Try this:

For a value in A1

This formula uses 5/4 rounding to the nearest muliple of 0.05
B1: =MROUND(A1,0.05)

Note: MROUND requires the Analysis ToolPak to be enabled.
If that doesn't appeal to you...try this:
B1: =ROUND(A1/5,2)*5

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[joeu2004]

looking for the formula that will round up to the nearest nickel.
For example, have a price of $2.21 would round to $2.20 and $2.23 would
round to $2.25.

I hasten to note your first example is "rounding", not "rounding up".
Since you want rounding, some choices are (where D1 contains the
unrounded dollar amount):

=mround(D1, 0.05)

=0.05*round(D1/0.05,0)

Note that MROUND() requires that you install the Excel Analysis ToolPak
add-in. See the MROUND Help page for details.

Also note that as written above, MROUND() will not work for negative
dollar amounts. The following works with both negative and positive
dollar amounts:

=mround(D1,sign(D1)*0.05)

Finally, note that for negative dollar amounts, the formulas above
round the negative value as if the value were positive with a minus
sign in front. For example, -2.21 becomes -2.20 and -2.23 becomes
-2.25.

 

[JM]

Yea, my posting wasnt exactly correct, but the previous responses got me
going in the right direction and was able to figure out that using the
roundup function worked.
thanks!

 

[JM]

Ok, I am using the following formula =ROUNDUP(A1/0.05,0)*0.05 and it is
working with the exception of the following example:

When I have a price of $1.60 it is rounding up to $1.65, but I want it to
stay at $1.60. But if it is $1.61, I need it to round up to $1.65. In
other words if the price ends in 0 or 5, I need the price to stay the same,
but otherwise I need it to round up to the next nickel.

 

[Ron Rosenfeld]

Ok, I am using the following formula =ROUNDUP(A1/0.05,0)*0.05 and it is
working with the exception of the following example:

When I have a price of $1.60 it is rounding up to $1.65, but I want it to
stay at $1.60. But if it is $1.61, I need it to round up to $1.65. In
other words if the price ends in 0 or 5, I need the price to stay the same,
but otherwise I need it to round up to the next nickel.

Your formula applied to 1.60 returns 1.60

Most likely, what you think is 1.60 is not really 1.60. It's probably the
result of some formula that is actually returning a value somewhat greater than
1.60.

Reformat the cell where you see 1.60 to show more decimals, and you'll likely
see what I'm writing about.
--ron