Rounding off to .48 or .98

[Mark4253]

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,

 

[Ron Coderre]

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

 

[JMB]

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

 

[Martin Fishlock]

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.

 

[Mark4253]

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

 

[Ron Coderre]

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

 

[David Biddulph]

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.

 

[JMB]

Which brings us back to what I have already suggested <g>

 

[Mark4253]

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

 

[Mark4253]

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