|
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 05:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com