IF Formula ????????

[Andy K]

I would like to increase the mark up on spares based on the cost price of the
part.
Example
£0.00 - 10.00 = 200%
£11.00-£50.00 = 100%
£51.00-£100.00 = 85%
£101.00-£200.00 = 60%
£201.00-£300.00 - 45%
£301.00-£500.00 = 30%
£501.00-£1000.00 = 35%
£1001.00-£10000.00 = 25%

therefore if the cost of a spare part in cell A1 =£400.00 cell A2 should
read £520.00

Thanks in anticipation Andy

 

[Stefi]

Create a table like this
0 200%
11 100%
51 85%
101 60%
201 45%
301 30%
501 35%
1001 25%

in Sheet2, then formula in A2:

=A1*(1+VLOOKUP(A1,Sheet2!A1:B8,2))

Regards,
Stefi

„Andy K†ezt írta:

 

[David Biddulph]

What about between 10.00 and 11.00 or between 50.00 and 51.00 or ... or
greater than 10000?
Or are the inputs contrained to being integers and to being no greater than
10k?

 

[Per Jessen]

Hi Andy

Set up a table in any sheet with three columns.
In first column goes lower values (0, 11, 51 etc.) in first column. In next
column you enter upper value (10, 50, 100 etc.) In the last column you have
the mark up. Name the first column range "From", middle coumn range "To" and
last column range "MarkUp"

To name a range select the cells goto Insert > Name > Define

You can set up the table in any sheet.

Enter this formula in A2 :

=A1*(1+SUMPRODUCT(--(From<=A1),--(A1<=To),MarkUp))

Hopes this helps.

 

[francis]

try thi
=(D2*IF(D2<11,200%,IF(D2<51,100%,IF(D2<101,85%,IF(D2<201,60%,IF(D2<301,45%,IF(D2<501,30%,IF(D2<1001,35%,25%))))))))+D2

change D2 to A1
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[Andy K]

Thank you Francis this was a great help, it worked fine

 

[Andy K]

Thanks I'll give this a go

Hi Andy

Set up a table in any sheet with three columns.
In first column goes lower values (0, 11, 51 etc.) in first column. In next
column you enter upper value (10, 50, 100 etc.) In the last column you have
the mark up. Name the first column range "From", middle coumn range "To" and
last column range "MarkUp"

To name a range select the cells goto Insert > Name > Define

You can set up the table in any sheet.

Enter this formula in A2 :

=A1*(1+SUMPRODUCT(--(From<=A1),--(A1<=To),MarkUp))

Hopes this helps.

 

[Andy K]

Hi David

you could use equal to or less than 10 and greater than 10. you could also
assume parts equal to or greater than 1000 would be @25%

Thanks

Andy