Formula for adding correct percentage.

[eddy]

I need to produce a conditional sell price
dependant upont the cost price.
E.G. A10 Could contain a cost price between £2 and £500.
If the cost is between £2 - £20 I would like a sell price in A2 to be cost price plus 20%.
If the cost is between £20 - £100 I would like a sell price in A2 to be cost price plus 15%.
If the cost is between £100 - £500 I would like a sell price in A2 to be cost price plus 10%.
Whats the formula please ?

Thanks Ted

 

[Dave Hawley]

Hi Eddy

Not tested, but try:


=IF(A1>100,A1*0.1,IF(A1>20,A1*0.15,A1*0.2))


You may also want to look at a "Validation list" to prevent amoumts over 500 etc See Data>Validation

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

This message was edited by Dave Hawley on 2002-03-24 07:42

 

[Dave Patton]

You can do such a calculation many ways.
First, a question. What do you want to do if cost is < 2 or > 500?

The formula ignores lower and upper factors.
Check the formula at the thresholds. What is the correct rate at 20, 100?


Other excellent solutions include:
- VLookup, Hlookup, or Lookup.
- If

=A10*((A10>0)*0.2-(A10>20)*0.05-(A10>100)*0.05)

 

[Legacy 98055]

Try this:

=IF(AND(A10<=20,A10>=2),A10+(A10*0.2),IF(AND(A10<=100,A10>20),A10+(A10*0.15),IF(AND(A10<=500,A10>100),A10+(A10*0.1),"Invalid Number")))

Tom
This message was edited by TsTom on 2002-03-24 07:47

 

[Dave Patton]

sorry

I did not add the original amount

=A10+A10*((A10>0)*0.2-(A10>20)*0.05-(A10>100)*0.05)

=IF(A10>100,A10*1.1,IF(AND(A10<=100,A10>20),A10*1.15,A10*1.2))
This message was edited by Dave Patton on 2002-03-24 07:54

 

[Yogi Anand]

Hi Ted:
Here we go
=IF(cost>500,"NA",IF(cost>=100,cost*110%,IF(cost>=20,cost*115%,IF(cost>=2,cost*120%,"NA"))))

HTH
Please post back if it works for you ... otherwise explain a little further and let us take it from there!

 

[eddy]

GREAT !!!!!!
Thanks all. So many different ways! They all seem to work fine.
Thanks again for everyones efforts and time
Regards Eddy

 

[Dave Hawley]

Oh bugger, I did the same as Dave Patton, I forgot to add the original amount. Just add one to the percentage. EG:

=IF(A1>100,A1*1.1,IF(A1>20,A1*1.15,A1*1.2))