Pricing formula

[Guest]

Hi everyone and thanks for any help you can give.
I am using Excel 2003 to set retail pricing for products in my store.
I have a column where I list my cost, then another column with a formula
that automatically computes my retail price based on the profit percentage I
designate.
I need a formula to also round the computed retail price to the nearest
xxx.95.
For instance, if the retail price formula produces an answer of $188.00, I
want it to round that to $187.95. Similarly, if the formula produces $188.70,
I want it to round it to $188.95.
I've looked at both FLOOR and CEILING but I can't seem to make them work for
what I'm trying to accomplish.
Any ideas?
Thanks again in advance.

 

[Guest]

All you need to do is Round your retail price to the nearest dollar, then
subtract 5 cents.

=ROUND(A1,0)-0.05

HTH,
Elkar

 

[David Biddulph]

For values between xx.45 and xx.50 this isn't strictly the nearest xx.95,
but may well be good enough.
An alternative may be =ROUND(A1+0.05,0)-0.05

 

[Pete_UK]

When would you stop rounding down? In your example you said that
$188.00 should be rounded down to $187.95, but what about $188.01,
$188.10, $188.25 ??

Pete

 

[Guest]

Pete:
$188.49 would round down to $187.95.
$188.50 would round up to $188.95.
Thanks

 

[Pete_UK]

Then the formula that Elkar gave you will do this, i.e.:

=ROUND(A1,0)-0.05

where A1 contains your computed retail price. You might like to
format the cell with the formula in as Currency with 2 dp, and then
copy it down for as many rows as you have.

Pete