Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If then, If then, If then.....
I can make the first "If" function work but when I want to add to it I keep
getting an error. Not sure which function I should be using? If B6 = "1 x 4" then divide D6/12 then multiply times 0.17, If B6 = "2 x 2" then divide D6/12 then multiply times 0.12, If B6 = "2 x 4" then divide D6/12 then multiply times 0.23, ........ I have 9 total to enter but I can narrow it down if I can only do 7 in one strand. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If then, If then, If then.....
=IF(B6=2*2;D6/12*0.17;IF(B6=2*4;D6/12*0.23))
You should nest "if" like above inside previous If. In Excel2003 the limitation of nesting is 7. -- R. Khoshravan Please click "Yes" if it is helpful. "Jackie" wrote: I can make the first "If" function work but when I want to add to it I keep getting an error. Not sure which function I should be using? If B6 = "1 x 4" then divide D6/12 then multiply times 0.17, If B6 = "2 x 2" then divide D6/12 then multiply times 0.12, If B6 = "2 x 4" then divide D6/12 then multiply times 0.23, ........ I have 9 total to enter but I can narrow it down if I can only do 7 in one strand. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If then, If then, If then.....
If you have more than a few of these, it is better to set up a little
table somewhere (assume M1:N9) made up like this: 1 x 4 0.17 2 x 2 0.12 2 x 4 0.23 and so on for your other 6 values. Then you can just use this simple formula instead of multiple IFs: =VLOOKUP(B6,M$1:N$9,2,0) If B6 is not exactly the same as the entries in column M (or blank), this will return an error - you can avoid that by doing this: =IF(ISERROR(VLOOKUP(B6,M$1:N$9,2,0))," wrong",VLOOKUP(B6,M$1:N$9,2,0)) Change the message to suit. Hope this helps. Pete On Feb 4, 1:18*pm, Jackie wrote: I can make the first "If" function work but when I want to add to it I keep getting an error. *Not sure which function I should be using? If B6 = "1 x 4" then divide D6/12 then multiply times 0.17, If B6 = "2 x 2" then divide D6/12 then multiply times 0.12, If B6 = "2 x 4" then divide D6/12 then multiply times 0.23, *........ I have 9 total to enter but I can narrow it down if I can only do 7 in one strand. * |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If then, If then, If then.....
I'm sorry, I didn't complete this. You want this formula:
=VLOOKUP(B6,M$1:N$9,2,0)*D6/12 or this one: =IF(ISERROR(VLOOKUP(B6,M$1:N$9,2,0))," wrong",VLOOKUP(B6,M$1:N$9,2,0) *D6/12) Hope this helps. Pete On Feb 4, 1:32*pm, Pete_UK wrote: If you have more than a few of these, it is better to set up a little table somewhere (assume M1:N9) made up like this: 1 x 4 * * * * *0.17 2 x 2 * * * * *0.12 2 x 4 * * * * *0.23 and so on for your other 6 values. Then you can just use this simple formula instead of multiple IFs: =VLOOKUP(B6,M$1:N$9,2,0) If B6 is not exactly the same as the entries in column M (or blank), this will return an error - you can avoid that by doing this: =IF(ISERROR(VLOOKUP(B6,M$1:N$9,2,0))," wrong",VLOOKUP(B6,M$1:N$9,2,0)) Change the message to suit. Hope this helps. Pete On Feb 4, 1:18*pm, Jackie wrote: I can make the first "If" function work but when I want to add to it I keep getting an error. *Not sure which function I should be using? If B6 = "1 x 4" then divide D6/12 then multiply times 0.17, If B6 = "2 x 2" then divide D6/12 then multiply times 0.12, If B6 = "2 x 4" then divide D6/12 then multiply times 0.23, *........ I have 9 total to enter but I can narrow it down if I can only do 7 in one strand. *- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|