MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Rounding dollar amounts upward to the nearest nickel


Posted by todd on February 02, 2002 5:34 PM

I can't figure out how to get my dollar amounts rounded upward in .05 increments. I am using the formula =(a multier)*A1*B1 to come up with different price levels for columns c,d,and e. The dollar amounts don't round out with my variables, and I would like to have each price level rounded up to the nearest nickel. Can anyone help me out with a formula?


Posted by Tom Urtis on February 02, 2002 6:10 PM

Not sure if you mean that you want to round up to the next nickel (i.e. 25.01 becomes 25.05), or just to the nearest nickel (25.01 becomes 25.00). Here's an example for each result.

Nearest:
=ROUND(A1/0.05,0)*0.05

Up to next:
=CEILING(A1,0.05)

Any help?

Tom Urtis

Posted by todd on February 03, 2002 7:26 AM

I am trying to round upward in .05 increments. For example, the formula to get my results in cell D5 is =2.5*B5*C5 which when formated for currency, leaves me the result of $20.86. I would like to have all of my dollar amounts rounded to the next highest .05 increment. (20.86 would become 20.90, etc... I tried your formula, but still couldn't get it to work. I am a newbie, so maybe it is something I am doing on my end. Is there a certain way that I need to add this formula to the existing formula in order to make both of them work?

Posted by Tom Urtis on February 03, 2002 9:08 AM

See if this does it for you.

=CEILING(2.5*B5*C5,0.05)

Tom Urtis

Posted by todd on February 03, 2002 9:22 AM

=CEILING(2.5*B5*C5,0.05)

Yes!!!!
Thank you so much Tom. I (newbie) was racking my brain trying to get this to work. Your formula did the job!
Thanks again,
todd.