price list changes using conditional functions?
 

I'm working on changing my retail price lists. I want to take my cost plus
tax and shipping and create conditional profit margins. For example, if my
cost is less than 2.00, I want to divide the cost by .68. If the cost is
between 2.00 and 3.00, I want to divide the cost by .7, and if the cost is
greater than 3.00, I want do divide by .77. It's important that the format
stays the same, so I can still easily print out my price tags. So, I want
Excel to look at my cost, evaluate what margin it should be at, and enter the
correct retail price in the next cell. Can anyone help I'm using Excel 2003.
Thanks!

price list changes using conditional functions?
 

Try this...

A1 = some price

=ROUND(A1/LOOKUP(A1,{0;2;3.01},{0.68;0.7;0.77}),2)

--
Biff
Microsoft Excel MVP


"Frustratedpricer" wrote in
message ...
I'm working on changing my retail price lists. I want to take my cost plus
tax and shipping and create conditional profit margins. For example, if my
cost is less than 2.00, I want to divide the cost by .68. If the cost is
between 2.00 and 3.00, I want to divide the cost by .7, and if the cost is
greater than 3.00, I want do divide by .77. It's important that the format
stays the same, so I can still easily print out my price tags. So, I want
Excel to look at my cost, evaluate what margin it should be at, and enter
the
correct retail price in the next cell. Can anyone help I'm using Excel
2003.
Thanks!

price list changes using conditional functions?
 

Hi,

If your cost is in A1, in B1 enter the formula

=A1/CHOOSE(A1,0.68,0.7,0.77)

and format the cell to 2 decimals.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Frustratedpricer" wrote:

I'm working on changing my retail price lists. I want to take my cost plus

price list changes using conditional functions?
 

if my cost is less than 2.00...divide the cost by .68
If the cost is between 2.00 and 3.00...divide the cost by .7
if the cost is greater than 3.00...divide by .77
=A1/CHOOSE(A1,0.68,0.7,0.77)

That will return an incorrect result when the cost is <1, when the the cost
=3 and when the cost is =4.

Did you even test that?

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

If your cost is in A1, in B1 enter the formula

=A1/CHOOSE(A1,0.68,0.7,0.77)

and format the cell to 2 decimals.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Frustratedpricer" wrote:

I'm working on changing my retail price lists. I want to take my cost
plus