If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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(A25,5,IF(A210,A2*1.35,IF(A250,A2*1.3,IF(A2 100,A2*1.25,IF(A2150,A2*1.2,IF(A2200,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 |
#3
|
|||
|
|||
Formulae Help
Try one of these optiions
--With query value in cell C1 =IF(C15,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(C15,5,C1+(C1*LOOKUP(C1,A1:A6,B1:B6))) OR (handling values more than 200) =IF(C15,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 |
#4
|
|||
|
|||
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(C15,5,C1+(C1*LOOKUP(C1,$A$1:$A$6,$B$1:$B$6))) =IF(C15,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(C15,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(C15,5,C1+(C1*LOOKUP(C1,A1:A6,B1:B6))) OR (handling values more than 200) =IF(C15,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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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(C15,5,C1+(C1*LOOKUP(C1,$A$1:$A$6,$B$1:$B$6))) =IF(C15,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(C15,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(C15,5,C1+(C1*LOOKUP(C1,A1:A6,B1:B6))) OR (handling values more than 200) =IF(C15,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 |
#7
|
|||
|
|||
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(A25,5,IF(A210,A2*1.35,IF(A250,A2*1.3,IF(A2 100,A2*1.25,IF(A2150,A2*1.2,IF(A2200,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 |
Thread Tools | |
Display Modes | |
|
|