Formulae Help
 

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

Formulae Help
 

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

Formulae Help
 

Try one of these optiions

--With query value in cell C1

=IF(C1<5,5,C1+(C1*LOOKUP(C1,{0,5,10,50,100,150},{0 ,0.35,0.3,0.25,0.2,0.15})))

OR
--with a table as below in A1:B6
Col A Col B
0 0%
5 35%
10 30%
50 25%
100 20%
150 15%

=IF(C1<5,5,C1+(C1*LOOKUP(C1,A1:A6,B1:B6)))

OR (handling values more than 200)

=IF(C1<5,5,IF(C1200,"to be calculated",C1+(C1*LOOKUP(C1,A1:A6,B1:B6))))

--
Jacob


"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

Formulae Help
 

Hi Jacob,

I like your options. However in the formulas with the table the
lookup_vector and result_Vector ranges need to be absolute.

=IF(C1<5,5,C1+(C1*LOOKUP(C1,$A$1:$A$6,$B$1:$B$6)))

=IF(C1<5,5,IF(C1200,"to be
calculated",C1+(C1*LOOKUP(C1,$A$1:$A$6,$B$1:$B$6)) ))

--
Regards,

OssieMac


"Jacob Skaria" wrote:

Try one of these optiions

--With query value in cell C1

=IF(C1<5,5,C1+(C1*LOOKUP(C1,{0,5,10,50,100,150},{0 ,0.35,0.3,0.25,0.2,0.15})))

OR
--with a table as below in A1:B6
Col A Col B
0 0%
5 35%
10 30%
50 25%
100 20%
150 15%

=IF(C1<5,5,C1+(C1*LOOKUP(C1,A1:A6,B1:B6)))

OR (handling values more than 200)

=IF(C1<5,5,IF(C1200,"to be calculated",C1+(C1*LOOKUP(C1,A1:A6,B1:B6))))

--
Jacob


"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

Formulae Help
 

On Sun, 7 Feb 2010 14:33:01 -0800, 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

Set up a lookup table someplace on your worksheet that looks like:

0 0%
5 35%
10 30%
50 25%
100 20%
150 15%

I placed these values in E1:F6

(You may have to change this a bit as you do not define what to do if the cost
is greater than 200, or at some of the other break points, but this should get
you started).

Then use the formula:

=A1+A1*VLOOKUP(A1,$E$1:$F$6,2)
--ron

Formulae Help
 

Hi dear, thanks for pointing that out.

--
Jacob


"OssieMac" wrote:

Hi Jacob,

I like your options. However in the formulas with the table the
lookup_vector and result_Vector ranges need to be absolute.

=IF(C1<5,5,C1+(C1*LOOKUP(C1,$A$1:$A$6,$B$1:$B$6)))

=IF(C1<5,5,IF(C1200,"to be
calculated",C1+(C1*LOOKUP(C1,$A$1:$A$6,$B$1:$B$6)) ))

--
Regards,

OssieMac


"Jacob Skaria" wrote:

Try one of these optiions

--With query value in cell C1

=IF(C1<5,5,C1+(C1*LOOKUP(C1,{0,5,10,50,100,150},{0 ,0.35,0.3,0.25,0.2,0.15})))

OR
--with a table as below in A1:B6
Col A Col B
0 0%
5 35%
10 30%
50 25%
100 20%
150 15%

=IF(C1<5,5,C1+(C1*LOOKUP(C1,A1:A6,B1:B6)))

OR (handling values more than 200)

=IF(C1<5,5,IF(C1200,"to be calculated",C1+(C1*LOOKUP(C1,A1:A6,B1:B6))))

--
Jacob


"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

Formulae Help
 

Hi, thankyou for your help it works but can you help me to do the table as
like you say it'll be easier to deal with the changes in the future.

"OssieMac" wrote:

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