| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding off to .48 or .98
Hi!
My company is increasing prices 3%. My boss likes everything rounded off to the nearest 48 cents or 98 cents. Is there a formula for increasing our prices 3% then rounding it off to the nearest 48 cents or 98 cents, whichever is closer after being increased 3%. Thanks, -- Mark |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rounding off to .48 or .98
Maybe something like this?:
With A1: (original price) This formula rounds up to the nearest $0.48 or $0.98 B1: =CEILING(A1*1.03,0.5)-0.02 Does that help? *********** Regards, Ron XL2002, WinXP "Mark4253" wrote: Hi! My company is increasing prices 3%. My boss likes everything rounded off to the nearest 48 cents or 98 cents. Is there a formula for increasing our prices 3% then rounding it off to the nearest 48 cents or 98 cents, whichever is closer after being increased 3%. Thanks, -- Mark |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rounding off to .48 or .98
This seemed to give the results you wanted:
=MROUND(A1*1.03+0.02,0.5)-0.02 Or, you may want to round the result after the 3% increase before rounding to the nearest 0.48 or 0.98: =MROUND(ROUND(A1*1.03,2)+0.02,0.5)-0.02 "Mark4253" wrote: Hi! My company is increasing prices 3%. My boss likes everything rounded off to the nearest 48 cents or 98 cents. Is there a formula for increasing our prices 3% then rounding it off to the nearest 48 cents or 98 cents, whichever is closer after being increased 3%. Thanks, -- Mark |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rounding off to .48 or .98
Mark,
Do you round up down or off. This is quite important and you may ineffect loose margin. You may also need to consider sales taxes if your prices are after tax. Why because the calculation of the the tax can cause a difference of 0.01 when you work backwards. But the replies from Ron and JMB are good. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Mark4253" wrote: Hi! My company is increasing prices 3%. My boss likes everything rounded off to the nearest 48 cents or 98 cents. Is there a formula for increasing our prices 3% then rounding it off to the nearest 48 cents or 98 cents, whichever is closer after being increased 3%. Thanks, -- Mark |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rounding off to .48 or .98
Hi Everybody!
Thanks to all for your replies. What I actually want to do is to first increase our prices 3% and then round off to the nearest 48 cents or 98 cents. For instance, if after increasing a price 3% it comes out to lets say, $48.05, I'd like to round it down to $47.98. However, if after increasing a price 3% it comes out to $48.25 I'd like to round up to $48.48. I'm going to try these formulas that were suggested to see how they work. With the formulas suggested, do you think this will work? If they don't can you suggest something else. Thanks again, Mark -- Mark "Martin Fishlock" wrote: Mark, Do you round up down or off. This is quite important and you may ineffect loose margin. You may also need to consider sales taxes if your prices are after tax. Why because the calculation of the the tax can cause a difference of 0.01 when you work backwards. But the replies from Ron and JMB are good. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Mark4253" wrote: Hi! My company is increasing prices 3%. My boss likes everything rounded off to the nearest 48 cents or 98 cents. Is there a formula for increasing our prices 3% then rounding it off to the nearest 48 cents or 98 cents, whichever is closer after being increased 3%. Thanks, -- Mark |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rounding off to .48 or .98
Try this:
With A1: (original price) This formula requires the Analysis Toolpak to be enabled B1: =MROUND(A1*1.03,0.5)-0.02 This formula does not B1: =ROUND(A1*1.03/0.5,0)*0.5-0.02 Does that help? *********** Regards, Ron XL2002, WinXP "Mark4253" wrote: Hi Everybody! Thanks to all for your replies. What I actually want to do is to first increase our prices 3% and then round off to the nearest 48 cents or 98 cents. For instance, if after increasing a price 3% it comes out to lets say, $48.05, I'd like to round it down to $47.98. However, if after increasing a price 3% it comes out to $48.25 I'd like to round up to $48.48. I'm going to try these formulas that were suggested to see how they work. With the formulas suggested, do you think this will work? If they don't can you suggest something else. Thanks again, Mark -- Mark "Martin Fishlock" wrote: Mark, Do you round up down or off. This is quite important and you may ineffect loose margin. You may also need to consider sales taxes if your prices are after tax. Why because the calculation of the the tax can cause a difference of 0.01 when you work backwards. But the replies from Ron and JMB are good. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Mark4253" wrote: Hi! My company is increasing prices 3%. My boss likes everything rounded off to the nearest 48 cents or 98 cents. Is there a formula for increasing our prices 3% then rounding it off to the nearest 48 cents or 98 cents, whichever is closer after being increased 3%. Thanks, -- Mark |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rounding off to .48 or .98
Perhaps modify Ron's formulae a bit.
=MROUND(A1*1.03+0.02,0.5)-0.02 would enable 8.24 to round to the nearest, being 8.48, rather than to 7.98. -- David Biddulph "Ron Coderre" wrote in message ... Try this: With A1: (original price) This formula requires the Analysis Toolpak to be enabled B1: =MROUND(A1*1.03,0.5)-0.02 This formula does not B1: =ROUND(A1*1.03/0.5,0)*0.5-0.02 Does that help? *********** Regards, Ron XL2002, WinXP "Mark4253" wrote: Hi Everybody! Thanks to all for your replies. What I actually want to do is to first increase our prices 3% and then round off to the nearest 48 cents or 98 cents. For instance, if after increasing a price 3% it comes out to lets say, $48.05, I'd like to round it down to $47.98. However, if after increasing a price 3% it comes out to $48.25 I'd like to round up to $48.48. I'm going to try these formulas that were suggested to see how they work. With the formulas suggested, do you think this will work? If they don't can you suggest something else. Thanks again, Mark -- Mark "Martin Fishlock" wrote: Mark, Do you round up down or off. This is quite important and you may ineffect loose margin. You may also need to consider sales taxes if your prices are after tax. Why because the calculation of the the tax can cause a difference of 0.01 when you work backwards. But the replies from Ron and JMB are good. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Mark4253" wrote: Hi! My company is increasing prices 3%. My boss likes everything rounded off to the nearest 48 cents or 98 cents. Is there a formula for increasing our prices 3% then rounding it off to the nearest 48 cents or 98 cents, whichever is closer after being increased 3%. Thanks, -- Mark |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rounding off to .48 or .98
Which brings us back to what I have already suggested <g
"David Biddulph" wrote: Perhaps modify Ron's formulae a bit. =MROUND(A1*1.03+0.02,0.5)-0.02 would enable 8.24 to round to the nearest, being 8.48, rather than to 7.98. -- David Biddulph "Ron Coderre" wrote in message ... Try this: With A1: (original price) This formula requires the Analysis Toolpak to be enabled B1: =MROUND(A1*1.03,0.5)-0.02 This formula does not B1: =ROUND(A1*1.03/0.5,0)*0.5-0.02 Does that help? *********** Regards, Ron XL2002, WinXP "Mark4253" wrote: Hi Everybody! Thanks to all for your replies. What I actually want to do is to first increase our prices 3% and then round off to the nearest 48 cents or 98 cents. For instance, if after increasing a price 3% it comes out to lets say, $48.05, I'd like to round it down to $47.98. However, if after increasing a price 3% it comes out to $48.25 I'd like to round up to $48.48. I'm going to try these formulas that were suggested to see how they work. With the formulas suggested, do you think this will work? If they don't can you suggest something else. Thanks again, Mark -- Mark "Martin Fishlock" wrote: Mark, Do you round up down or off. This is quite important and you may ineffect loose margin. You may also need to consider sales taxes if your prices are after tax. Why because the calculation of the the tax can cause a difference of 0.01 when you work backwards. But the replies from Ron and JMB are good. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Mark4253" wrote: Hi! My company is increasing prices 3%. My boss likes everything rounded off to the nearest 48 cents or 98 cents. Is there a formula for increasing our prices 3% then rounding it off to the nearest 48 cents or 98 cents, whichever is closer after being increased 3%. Thanks, -- Mark |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rounding off to .48 or .98
Thanks for all the great suggesions everybody. Hopefully, I'll have some
time over the next day or so to try the suggested formulas and I'll let you know how they work out. Thanks, Mark -- Mark "JMB" wrote: Which brings us back to what I have already suggested <g "David Biddulph" wrote: Perhaps modify Ron's formulae a bit. =MROUND(A1*1.03+0.02,0.5)-0.02 would enable 8.24 to round to the nearest, being 8.48, rather than to 7.98. -- David Biddulph "Ron Coderre" wrote in message ... Try this: With A1: (original price) This formula requires the Analysis Toolpak to be enabled B1: =MROUND(A1*1.03,0.5)-0.02 This formula does not B1: =ROUND(A1*1.03/0.5,0)*0.5-0.02 Does that help? *********** Regards, Ron XL2002, WinXP "Mark4253" wrote: Hi Everybody! Thanks to all for your replies. What I actually want to do is to first increase our prices 3% and then round off to the nearest 48 cents or 98 cents. For instance, if after increasing a price 3% it comes out to lets say, $48.05, I'd like to round it down to $47.98. However, if after increasing a price 3% it comes out to $48.25 I'd like to round up to $48.48. I'm going to try these formulas that were suggested to see how they work. With the formulas suggested, do you think this will work? If they don't can you suggest something else. Thanks again, Mark -- Mark "Martin Fishlock" wrote: Mark, Do you round up down or off. This is quite important and you may ineffect loose margin. You may also need to consider sales taxes if your prices are after tax. Why because the calculation of the the tax can cause a difference of 0.01 when you work backwards. But the replies from Ron and JMB are good. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Mark4253" wrote: Hi! My company is increasing prices 3%. My boss likes everything rounded off to the nearest 48 cents or 98 cents. Is there a formula for increasing our prices 3% then rounding it off to the nearest 48 cents or 98 cents, whichever is closer after being increased 3%. Thanks, -- Mark |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rounding off to .48 or .98
Hi Everybody!
I just wanted to thank everyone who posted for their help. The formual that works is the one suggested by JMB: Or, you may want to round the result after the 3% increase before rounding to the nearest 0.48 or 0.98: =MROUND(ROUND(A1*1.03,2)+0.02,0.5)-0.02 This will sure make it much easier to increase our pricing. Thanks again, Mark -- Mark "Mark4253" wrote: Hi! My company is increasing prices 3%. My boss likes everything rounded off to the nearest 48 cents or 98 cents. Is there a formula for increasing our prices 3% then rounding it off to the nearest 48 cents or 98 cents, whichever is closer after being increased 3%. Thanks, -- Mark |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I correct rounding errors in Excel formulas? | Excel Worksheet Functions | |||
| Rounding and Number Formatting | Excel Discussion (Misc queries) | |||
| Rounding to the Nearest Eighth | Excel Discussion (Misc queries) | |||
| Percentage rounding error in charts | Excel Discussion (Misc queries) | |||
| Banker's Rounding - need help! | Excel Discussion (Misc queries) | |||
Linear Mode
