| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How many concurrent nested IF statments does Excel allow?
This formula works:
=IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55, IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0))))))) This formula does not, why? =IF(B40,IF(H4 449, 1.6, IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55, IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0))))))) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How many concurrent nested IF statments does Excel allow?
Hi!
The top formula has 7 nested levels which is the limit. The bottom formula has 8. How many conditions do you have in total? Biff "Loudmouth" wrote in message ... This formula works: =IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55, IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0))))))) This formula does not, why? =IF(B40,IF(H4 449, 1.6, IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55, IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0))))))) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How many concurrent nested IF statments does Excel allow?
As Biff has noted, the limit is 7 nested functions in a single formula. Most
people think this limit applies only to nested IF statements, but it is actually a limit on the total number of nested functions of any type. The second point to be made is that the limit is per formula. You could put a + symbol at the end of your first formula and then repeat that same formula and it would still work (although the result would be doubled) because each individual formula only has 7 nested levels. "Loudmouth" wrote: This formula works: =IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55, IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0))))))) This formula does not, why? =IF(B40,IF(H4 449, 1.6, IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55, IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0))))))) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How many concurrent nested IF statments does Excel allow?
I need about 25 to make it work. Can you suggest another way of comparing
that many numbers? "Biff" wrote: Hi! The top formula has 7 nested levels which is the limit. The bottom formula has 8. How many conditions do you have in total? Biff "Loudmouth" wrote in message ... This formula works: =IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55, IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0))))))) This formula does not, why? =IF(B40,IF(H4 449, 1.6, IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55, IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0))))))) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How many concurrent nested IF statments does Excel allow?
I don't know exactly what you're doing but you need to create a table. This
table is based on your bottom formula: 0............0..........0 124.....0.45.....0.35 149.....0.55.....0.45 199.....0.7.......0.55 249.....0.9.......0.7 299.....1.05.....0.85 349.....1.25.....1 399.....1.4 499.....1.6 The 2nd column would be used when B40 and the third column would be used when B4<=0. (at least, that's the logic of your IF formulas) =IF(B40,VLOOKUP(H4,D1:E9,2,1),VLOOKUP(H4,D1:F7,3, 1)) Where D1:E9 is the above table. Biff "Loudmouth" wrote in message ... I need about 25 to make it work. Can you suggest another way of comparing that many numbers? "Biff" wrote: Hi! The top formula has 7 nested levels which is the limit. The bottom formula has 8. How many conditions do you have in total? Biff "Loudmouth" wrote in message ... This formula works: =IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55, IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0))))))) This formula does not, why? =IF(B40,IF(H4 449, 1.6, IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55, IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0))))))) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How many concurrent nested IF statments does Excel allow?
This might be confusing:
=IF(B40,VLOOKUP(H4,D1:E9,2,1),VLOOKUP(H4,D1:F7,3 ,1)) Where D1:E9 is the above table. Those table ranges, D1:E9 and D1:F7, are based on the unequal number of entries in each column But I'm guessing that it's incomplete just because you ran into nesting problems. To make it less confusing just change it to: (and finish your table) =IF(B40,VLOOKUP(H4,D1:F9,2,1),VLOOKUP(H4,D1:F9,3, 1)) Biff "Biff" wrote in message ... I don't know exactly what you're doing but you need to create a table. This table is based on your bottom formula: 0............0..........0 124.....0.45.....0.35 149.....0.55.....0.45 199.....0.7.......0.55 249.....0.9.......0.7 299.....1.05.....0.85 349.....1.25.....1 399.....1.4 499.....1.6 The 2nd column would be used when B40 and the third column would be used when B4<=0. (at least, that's the logic of your IF formulas) =IF(B40,VLOOKUP(H4,D1:E9,2,1),VLOOKUP(H4,D1:F7,3, 1)) Where D1:E9 is the above table. Biff "Loudmouth" wrote in message ... I need about 25 to make it work. Can you suggest another way of comparing that many numbers? "Biff" wrote: Hi! The top formula has 7 nested levels which is the limit. The bottom formula has 8. How many conditions do you have in total? Biff "Loudmouth" wrote in message ... This formula works: =IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55, IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0))))))) This formula does not, why? =IF(B40,IF(H4 449, 1.6, IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55, IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0))))))) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How many concurrent nested IF statments does Excel allow?
I think the easiest way to solve this problem is going to be for you to set
up a lookup table with your associated values in it and then use VLOOKUP() inside of an IF() statement to pick your choices. This is difficult to explain in words, so I prepared an example workbook which you can get at: http://www.jlathamsite.com/uploads/v..._Loudmouth.xls your formula in the cell where you're trying to build it now is going to look something like =IF(B40,VLOOKUP(H4,H9:I16,2,True),VLOOKUP(H4,H9:J 16,3,True)) The ranges mentioned, H9:I16 and H9:J16, will need to be changed to match the range you set up in the real world. A couple of things to note, the list must be in ascending order from top to bottom based on your to-match values (399, 349, etc) in order for it to work properly and reliably. The lookup matrix does not have to be on the same sheet, you just have to be sure and reference the lookup range correctly. For more information, look for VLOOKUP under Excel Help. I hope this helps some. "Loudmouth" wrote: This formula works: =IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55, IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0))))))) This formula does not, why? =IF(B40,IF(H4 449, 1.6, IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55, IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0))))))) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How many concurrent nested IF statments does Excel allow?
Or even...
=VLOOKUP(H4,D1:F9,if(b40,2,3),1) Biff wrote: This might be confusing: =IF(B40,VLOOKUP(H4,D1:E9,2,1),VLOOKUP(H4,D1:F7,3 ,1)) Where D1:E9 is the above table. Those table ranges, D1:E9 and D1:F7, are based on the unequal number of entries in each column But I'm guessing that it's incomplete just because you ran into nesting problems. To make it less confusing just change it to: (and finish your table) =IF(B40,VLOOKUP(H4,D1:F9,2,1),VLOOKUP(H4,D1:F9,3, 1)) Biff "Biff" wrote in message ... I don't know exactly what you're doing but you need to create a table. This table is based on your bottom formula: 0............0..........0 124.....0.45.....0.35 149.....0.55.....0.45 199.....0.7.......0.55 249.....0.9.......0.7 299.....1.05.....0.85 349.....1.25.....1 399.....1.4 499.....1.6 The 2nd column would be used when B40 and the third column would be used when B4<=0. (at least, that's the logic of your IF formulas) =IF(B40,VLOOKUP(H4,D1:E9,2,1),VLOOKUP(H4,D1:F7,3, 1)) Where D1:E9 is the above table. Biff "Loudmouth" wrote in message ... I need about 25 to make it work. Can you suggest another way of comparing that many numbers? "Biff" wrote: Hi! The top formula has 7 nested levels which is the limit. The bottom formula has 8. How many conditions do you have in total? Biff "Loudmouth" wrote in message ... This formula works: =IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55, IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0))))))) This formula does not, why? =IF(B40,IF(H4 449, 1.6, IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55, IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0))))))) -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How many concurrent nested IF statments does Excel allow?
Outstanding! Thank you very much. I am now able to complete my project.
"JLatham" wrote: I think the easiest way to solve this problem is going to be for you to set up a lookup table with your associated values in it and then use VLOOKUP() inside of an IF() statement to pick your choices. This is difficult to explain in words, so I prepared an example workbook which you can get at: http://www.jlathamsite.com/uploads/v..._Loudmouth.xls your formula in the cell where you're trying to build it now is going to look something like =IF(B40,VLOOKUP(H4,H9:I16,2,True),VLOOKUP(H4,H9:J 16,3,True)) The ranges mentioned, H9:I16 and H9:J16, will need to be changed to match the range you set up in the real world. A couple of things to note, the list must be in ascending order from top to bottom based on your to-match values (399, 349, etc) in order for it to work properly and reliably. The lookup matrix does not have to be on the same sheet, you just have to be sure and reference the lookup range correctly. For more information, look for VLOOKUP under Excel Help. I hope this helps some. "Loudmouth" wrote: This formula works: =IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55, IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0))))))) This formula does not, why? =IF(B40,IF(H4 449, 1.6, IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55, IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0))))))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I generate the excel chart with out opening the excel instance? | Charts and Charting in Excel | |||
| Allow more than 7 nested IF THEN tests in EXCEL. Allow 30 or more | Excel Worksheet Functions | |||
| Do nested subtotals have an errror in Excel 2003 | Excel Discussion (Misc queries) | |||
| Excel nested IF formula question | Excel Discussion (Misc queries) | |||
| TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Linear Mode
