| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Long Long Long Nested If Function
Hello,
I need some help on a very long nested if formula. Or at least thats the type that I have been trying to use to get the calculations on my spreadsheet. I have this formula which is working for me but it is only one of the 5 variables I need to include in it. 1) =IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200)) )) to this formula I need to add =IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260)))) =IF(B5="lsp",IF(P23=20,1125,(IF(P23=40,1225,1550)) )) =IF(B5="mtrl",IF(P23=20,1035,(IF(P23=40,1150,1500) ))) =IF(B5="trto",IF(P23=20,1450,(IF(P23=40,1650,1950) ))) So I was wondering if there is a way to have all of these as ONE whole formula. I will appreciate your help very much. Thanks! -- sss |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Long Long Long Nested If Function
"sed" wrote:
I have this formula which is working for me but it is only one of the 5 variables I need to include in it. 1) =IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200)) )) to this formula I need to add =IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260)))) [....etc....] One approach.... Construct the following table in some unused range of cells: X1: cdy Y1: =IF(P23=20,1625, IF(P23=40,1875,2200)) X2: nwr Y2: =IF(P23=20,850, IF(P23=40,935,1260)) X3: lsp Y3: =IF(P23=20,1125, IF(P23=40,1225,1550)) X4: mtrl Y4: =IF(P23=20,1035, IF(P23=40,1150,1500)) X5: trto Y5: =IF(P23=20,1450, IF(P23=40,1650,1950)) Then, in the cell where you would have written the combined IF expression, enter: =VLOOKUP(B5, X1:Y5, 2, 0) If B5 might contain some undefined string, then: =IF(ISERROR(VLOOKUP(B5,X1:Y5,2, 0)), "", VLOOKUP(B5,X1:Y5,2, 0)) So I was wondering if there is a way to have all of these as ONE whole formula. Arguably, the formulas in Y1:Y5 can be "simplified", albeit error-prone: Y1: =2200 - 575*(P3=20) - 325*(P3=40) Y2: =1260 - 410*(P3=20) - 325*(P3=40) Y3: =1550 - 425*(P3=20) - 325*(P3=40) Y4: =1500 - 465*(P3=20) - 350*(P3=40) Y5: =1950 - 500*(P3=20) - 300*(P3=40) That lends itself to the following formula without the need for a table: =CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"} ,0), 2200 - 575*(P3=20) - 325*(P3=40), 1260 - 410*(P3=20) - 325*(P3=40), 1550 - 425*(P3=20) - 325*(P3=40), 1500 - 465*(P3=20) - 350*(P3=40), 1950 - 500*(P3=20) - 300*(P3=40)) With error-checking: =IF(ISERROR(MATCH(B5,{"cdy","nwr","lsp","mtrl","tr to"},0)), "", CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"}, 0), 2200 - 575*(P3=20) - 325*(P3=40), 1260 - 410*(P3=20) - 325*(P3=40), 1550 - 425*(P3=20) - 325*(P3=40), 1500 - 465*(P3=20) - 350*(P3=40), 1950 - 500*(P3=20) - 300*(P3=40)) ----- original message ----- "sed" wrote: Hello, I need some help on a very long nested if formula. Or at least thats the type that I have been trying to use to get the calculations on my spreadsheet. I have this formula which is working for me but it is only one of the 5 variables I need to include in it. 1) =IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200)) )) to this formula I need to add =IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260)))) =IF(B5="lsp",IF(P23=20,1125,(IF(P23=40,1225,1550)) )) =IF(B5="mtrl",IF(P23=20,1035,(IF(P23=40,1150,1500) ))) =IF(B5="trto",IF(P23=20,1450,(IF(P23=40,1650,1950) ))) So I was wondering if there is a way to have all of these as ONE whole formula. I will appreciate your help very much. Thanks! -- sss |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Long Long Long Nested If Function
PS....
I wrote: Construct the following table in some unused range of cells: X1: cdy Y1: =IF(P23=20,1625, IF(P23=40,1875,2200)) [....etc....] Arguably, the formulas in Y1:Y5 can be "simplified", albeit error-prone: Y1: =2200 - 575*(P3=20) - 325*(P3=40) [....etc....] That lends itself to the following formula without the need for a table: =CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"} ,0), 2200 - 575*(P3=20) - 325*(P3=40), [....etc....] Of course, you can use the CHOOSE/MATCH combination with the original IF expressions, to wit: CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"}, 0), IF(P23=20,1625, IF(P23=40,1875,2200)), IF(P23=20,850, IF(P23=40,935,1260)), IF(P23=20,1125, IF(P23=40,1225,1550)), IF(P23=20,1035, IF(P23=40,1150,1500)), IF(P23=20,1450, IF(P23=40,1650,1950))) ----- original message ----- "Joe User" wrote: "sed" wrote: I have this formula which is working for me but it is only one of the 5 variables I need to include in it. 1) =IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200)) )) to this formula I need to add =IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260)))) [....etc....] One approach.... Construct the following table in some unused range of cells: X1: cdy Y1: =IF(P23=20,1625, IF(P23=40,1875,2200)) X2: nwr Y2: =IF(P23=20,850, IF(P23=40,935,1260)) X3: lsp Y3: =IF(P23=20,1125, IF(P23=40,1225,1550)) X4: mtrl Y4: =IF(P23=20,1035, IF(P23=40,1150,1500)) X5: trto Y5: =IF(P23=20,1450, IF(P23=40,1650,1950)) Then, in the cell where you would have written the combined IF expression, enter: =VLOOKUP(B5, X1:Y5, 2, 0) If B5 might contain some undefined string, then: =IF(ISERROR(VLOOKUP(B5,X1:Y5,2, 0)), "", VLOOKUP(B5,X1:Y5,2, 0)) So I was wondering if there is a way to have all of these as ONE whole formula. Arguably, the formulas in Y1:Y5 can be "simplified", albeit error-prone: Y1: =2200 - 575*(P3=20) - 325*(P3=40) Y2: =1260 - 410*(P3=20) - 325*(P3=40) Y3: =1550 - 425*(P3=20) - 325*(P3=40) Y4: =1500 - 465*(P3=20) - 350*(P3=40) Y5: =1950 - 500*(P3=20) - 300*(P3=40) That lends itself to the following formula without the need for a table: =CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"} ,0), 2200 - 575*(P3=20) - 325*(P3=40), 1260 - 410*(P3=20) - 325*(P3=40), 1550 - 425*(P3=20) - 325*(P3=40), 1500 - 465*(P3=20) - 350*(P3=40), 1950 - 500*(P3=20) - 300*(P3=40)) With error-checking: =IF(ISERROR(MATCH(B5,{"cdy","nwr","lsp","mtrl","tr to"},0)), "", CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"}, 0), 2200 - 575*(P3=20) - 325*(P3=40), 1260 - 410*(P3=20) - 325*(P3=40), 1550 - 425*(P3=20) - 325*(P3=40), 1500 - 465*(P3=20) - 350*(P3=40), 1950 - 500*(P3=20) - 300*(P3=40)) ----- original message ----- "sed" wrote: Hello, I need some help on a very long nested if formula. Or at least thats the type that I have been trying to use to get the calculations on my spreadsheet. I have this formula which is working for me but it is only one of the 5 variables I need to include in it. 1) =IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200)) )) to this formula I need to add =IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260)))) =IF(B5="lsp",IF(P23=20,1125,(IF(P23=40,1225,1550)) )) =IF(B5="mtrl",IF(P23=20,1035,(IF(P23=40,1150,1500) ))) =IF(B5="trto",IF(P23=20,1450,(IF(P23=40,1650,1950) ))) So I was wondering if there is a way to have all of these as ONE whole formula. I will appreciate your help very much. Thanks! -- sss |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Long Long Long Nested If Function
Joe thanks a lot! I appreciate your help!
-- sss "Joe User" wrote: PS.... I wrote: Construct the following table in some unused range of cells: X1: cdy Y1: =IF(P23=20,1625, IF(P23=40,1875,2200)) [....etc....] Arguably, the formulas in Y1:Y5 can be "simplified", albeit error-prone: Y1: =2200 - 575*(P3=20) - 325*(P3=40) [....etc....] That lends itself to the following formula without the need for a table: =CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"} ,0), 2200 - 575*(P3=20) - 325*(P3=40), [....etc....] Of course, you can use the CHOOSE/MATCH combination with the original IF expressions, to wit: CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"}, 0), IF(P23=20,1625, IF(P23=40,1875,2200)), IF(P23=20,850, IF(P23=40,935,1260)), IF(P23=20,1125, IF(P23=40,1225,1550)), IF(P23=20,1035, IF(P23=40,1150,1500)), IF(P23=20,1450, IF(P23=40,1650,1950))) ----- original message ----- "Joe User" wrote: "sed" wrote: I have this formula which is working for me but it is only one of the 5 variables I need to include in it. 1) =IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200)) )) to this formula I need to add =IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260)))) [....etc....] One approach.... Construct the following table in some unused range of cells: X1: cdy Y1: =IF(P23=20,1625, IF(P23=40,1875,2200)) X2: nwr Y2: =IF(P23=20,850, IF(P23=40,935,1260)) X3: lsp Y3: =IF(P23=20,1125, IF(P23=40,1225,1550)) X4: mtrl Y4: =IF(P23=20,1035, IF(P23=40,1150,1500)) X5: trto Y5: =IF(P23=20,1450, IF(P23=40,1650,1950)) Then, in the cell where you would have written the combined IF expression, enter: =VLOOKUP(B5, X1:Y5, 2, 0) If B5 might contain some undefined string, then: =IF(ISERROR(VLOOKUP(B5,X1:Y5,2, 0)), "", VLOOKUP(B5,X1:Y5,2, 0)) So I was wondering if there is a way to have all of these as ONE whole formula. Arguably, the formulas in Y1:Y5 can be "simplified", albeit error-prone: Y1: =2200 - 575*(P3=20) - 325*(P3=40) Y2: =1260 - 410*(P3=20) - 325*(P3=40) Y3: =1550 - 425*(P3=20) - 325*(P3=40) Y4: =1500 - 465*(P3=20) - 350*(P3=40) Y5: =1950 - 500*(P3=20) - 300*(P3=40) That lends itself to the following formula without the need for a table: =CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"} ,0), 2200 - 575*(P3=20) - 325*(P3=40), 1260 - 410*(P3=20) - 325*(P3=40), 1550 - 425*(P3=20) - 325*(P3=40), 1500 - 465*(P3=20) - 350*(P3=40), 1950 - 500*(P3=20) - 300*(P3=40)) With error-checking: =IF(ISERROR(MATCH(B5,{"cdy","nwr","lsp","mtrl","tr to"},0)), "", CHOOSE(MATCH(B5,{"cdy","nwr","lsp","mtrl","trto"}, 0), 2200 - 575*(P3=20) - 325*(P3=40), 1260 - 410*(P3=20) - 325*(P3=40), 1550 - 425*(P3=20) - 325*(P3=40), 1500 - 465*(P3=20) - 350*(P3=40), 1950 - 500*(P3=20) - 300*(P3=40)) ----- original message ----- "sed" wrote: Hello, I need some help on a very long nested if formula. Or at least thats the type that I have been trying to use to get the calculations on my spreadsheet. I have this formula which is working for me but it is only one of the 5 variables I need to include in it. 1) =IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200)) )) to this formula I need to add =IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260)))) =IF(B5="lsp",IF(P23=20,1125,(IF(P23=40,1225,1550)) )) =IF(B5="mtrl",IF(P23=20,1035,(IF(P23=40,1150,1500) ))) =IF(B5="trto",IF(P23=20,1450,(IF(P23=40,1650,1950) ))) So I was wondering if there is a way to have all of these as ONE whole formula. I will appreciate your help very much. Thanks! -- sss |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Long Long Long Nested If Function
=IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200)) ),IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260))) ,IF(B5="lsp",IF(P23=20,1125,(IF(P23=40,1225,1550)) ),IF(B5="mtrl",IF(P23=20,1035,(IF(P23=40,1150,1500 ))),IF(B5="trto",IF(P23=20,1450,(IF(P23=40,1650,19 50))),"")))))
-- David Biddulph "sed" wrote in message ... Hello, I need some help on a very long nested if formula. Or at least thats the type that I have been trying to use to get the calculations on my spreadsheet. I have this formula which is working for me but it is only one of the 5 variables I need to include in it. 1) =IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200)) )) to this formula I need to add =IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260)))) =IF(B5="lsp",IF(P23=20,1125,(IF(P23=40,1225,1550)) )) =IF(B5="mtrl",IF(P23=20,1035,(IF(P23=40,1150,1500) ))) =IF(B5="trto",IF(P23=20,1450,(IF(P23=40,1650,1950) ))) So I was wondering if there is a way to have all of these as ONE whole formula. I will appreciate your help very much. Thanks! -- sss |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested IF too long - other suggestions? | Excel Worksheet Functions | |||
| Interrupting long calculations in Excel? Hi all, I am having a verylong calculation in Excel using VBA. Of course I know I can hit ESC orCTRL+BREAK to interrupt the VBA program. But I found due to too long runningprocess, the Excel window stop to | Excel Discussion (Misc queries) | |||
| HELP! with long function | Excel Worksheet Functions | |||
| Clearing cells takes long, long time | Excel Discussion (Misc queries) | |||
| Long Date - Long Date = text is days | Excel Discussion (Misc queries) | |||
Hybrid Mode
