IF Formula ????????
 

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

IF Formula ????????
 

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:

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

IF Formula ????????
 

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


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

IF Formula ????????
 

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.

---
Per

"Andy K" skrev i meddelelsen
...
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

IF Formula ????????
 

try this
=(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" wrote:

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

IF Formula ????????
 

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

"francis" wrote:

try this
=(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" wrote:

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

IF Formula ????????
 

Thanks I'll give this a go

"Per Jessen" wrote:

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.

---
Per

"Andy K" skrev i meddelelsen
...
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

IF Formula ????????
 

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

"David Biddulph" wrote:

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


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