Pricing formula
 

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.

Pricing formula
 

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


"Derby" wrote:

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.

Pricing formula
 

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
--
David Biddulph

"Elkar" wrote in message
...
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


"Derby" wrote:

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.

Pricing formula
 

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

On Nov 13, 8:51 pm, Derby wrote:
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.

Pricing formula
 

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

"Pete_UK" wrote:

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

On Nov 13, 8:51 pm, Derby wrote:
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.

Pricing formula
 

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

On Nov 13, 11:10 pm, Derby wrote:
Pete:
$188.49 would round down to $187.95.
$188.50 would round up to $188.95.
Thanks



"Pete_UK" wrote:
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

On Nov 13, 8:51 pm, Derby wrote:
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.- Hide quoted text -

- Show quoted text -