Pricing formula

D

Derby

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.
 
E

Elkar

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
 
D

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
 
P

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top