Rounding
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! |
Rounding
JM wrote:
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). |
Rounding
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. wrote in message ups.com... JM wrote: 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). |
Rounding
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 "JM" wrote: 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! |
Rounding
Another way,
Use ROUNDUP instead of ROUND. "JM" wrote: 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! |
Rounding
JM wrote:
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. |
All times are GMT +1. The time now is 03:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com