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 cost price in A2 to be cost price plus 20%.
If the cost is between £20 - £100 I would like a cost price in A2 to be cost price plus 15%.
If the cost is between £100 - £500 I would like a cost price in A2 to be cost price plus 10%.
Whats the formula please ?

Thanks Ted

[Aladin Akyurek]

On 2002-03-24 07:25, eddy wrote:
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 cost price in A2 to be cost price plus 20%.
If the cost is between £20 - £100 I would like a cost price in A2 to be cost price plus 15%.
If the cost is between £100 - £500 I would like a cost price in A2 to be cost price plus 10%.
Whats the formula please ?

Thanks Ted

Ted,

It's unclear what happens when

A10 = 1,
A10 > 500,

and if

A10 = 20, which mark-up should apply: 20% or 15% ?

Given these caveats, you can use either:

=A10*VLOOKUP(A10,{2,0.2;20,0.15;100,0.1},2)+A10

or, in a more controlled way:

=IF(AND(ISNUMBER(A10),A10>=2,A10<=500),A10*((VLOOKUP(A10,{2,0.2;20,0.15;100,0.1},2)+1)),"")

These formulas opt for 15% when A10 = 20 and 10% when A10 = 100.

Aladin



[ This Message was edited by: Aladin Akyurek 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