Rounding

[JM]

Let me try this again since I did a poor job explaining the first post.

I am working on a price list. I am allowed to round up to the next nickel
if the price is not on the nickel. So if the price is $1.60 or $1.65 it is
ok. But if the price is showing on the list at $1.61, I am allowed to round
that to $1.65.

The formula I am using is =ROUND(A1/0.05,0)*0.05 and it works for everything
that is not on the nickel. For those prices on the nickel, the formula is
rounding up to the next nickel. Meaning that $1.60 is rounding to $1.65.

What adjustment do I need to make to the formula?

thanks!

 

[joeu2004]

The formula I am using is =ROUND(A1/0.05,0)*0.05 and it works for everything
that is not on the nickel. For those prices on the nickel, the formula is
rounding up to the next nickel. Meaning that $1.60 is rounding to $1.65.
What adjustment do I need to make to the formula?

Works fine for me as-is. I am using Office Excel 2003 (11.5612.5606).

 

[JM]

I just noticed what is causing the problem...the column I am using for the
price is adding up the cost, markup and tax, thus has rounding issues there.
The $1.60 showing was actually $1.6012. When I hardkey the $1.60 it works.
So I need the price formula to round also.

 

[Guest]

Try this
Let say your A1 =1.6

Formula in B1 =CEILING(A1,0.05) will give you 1.6 If you change A1 to 1.61
the result will be 1.65

 

[Guest]

Another way,
Use ROUNDUP instead of ROUND.

 

[nomail1983]

I just noticed what is causing the problem...the column I am using for the
price is adding up the cost, markup and tax, thus has rounding issues there.
The $1.60 showing was actually $1.6012. When I hardkey the $1.60 it works.

I thought about that before -- the fact that what you see as $1.60
might not be exactly $1.60. But that should not adversely affect
ROUND() in this case. For example, round($1.6012/0.05,0)*0.05 is
$1.60. Since you used ROUNDUP() previously, I wonder if you misread
the formula and it is still using ROUNDUP() instead of ROUND(). In
that case, yes, $1.6012 would round up to $1.65, whereas $1.60 exactly
would not.