Hi KevL,

With your criteria of 10 < 50 then 50 <100. The question arises which
group does 50 belong to? Therfore I have given you 2 formulas and both work
on the upper limit only because as soon as a correct if match then the
formula stops. I have assumed that whatever value is less than 5 then the
result is to be 5.

The first formula sets eveything below the upper limit. That is 44.99 is
less than 50 so is 49.99 is included but 50 not included. The second formula
includes the upper limit. That is 50 is included.

Both formulas process the value in cell A2. Have also included an Else for
values either 200 and above for first formula or above 200 for second
formula. (Just for the exercise I assumed 12.5% for the Else.)

first formula
=IF(A2<5,5,IF(A2<10,A2*1.35,IF(A2<50,A2*1.3,IF(A2< 100,A2*1.25,IF(A2<150,A2*1.2,IF(A2<200,A2*1.15,A2* 1.125))))))


second formula
=IF(A2<=5,5,IF(A2<=10,A2*1.35,IF(A2<=50,A2*1.3,IF( A2<=100,A2*1.25,IF(A2<=150,A2*1.2,IF(A2<=200,A2*1. 15,A2*1.125))))))


Personally I would insert all the values in cells and use absolute
addressing for the cells containing the values. Then when you have it
correct, you only have to change the table of values to change the formula.

If you want more help to do this then let me know.



--
Regards,

OssieMac


"KevL" wrote:

Hi, I am trying to right a formulae for the following below: I think that I
should be using the IF but can't seem to make it work.

In my cell I will have a price of a part I want it to calculate the on cost
price to the customer using the table below:
<£5.00 = £5.00
<£10 +35% =
10 <50 +30% =
50 <100 +25% =
100 <150 +20% =
150 <200 +15% =

Example:

£5.00 = £6.75
£15.00 = £19.50