Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I correct rounding errors in Excel formulas? | Excel Worksheet Functions | |||
Problems: rounding & formatting Text/# combinations | Excel Discussion (Misc queries) | |||
Rounding to the Nearest Eighth | Excel Discussion (Misc queries) | |||
Rounding numbers to the nearest 5 or 0 | Excel Worksheet Functions | |||
Banker's Rounding - need help! | Excel Discussion (Misc queries) |