Home 
Search 
Today's Posts 
#1




nested if functions in Excel 2002
I need to nest 10 IF functions.
According to Excel help: Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the last of the following examples. What else can I do? 
#2




nested if functions in Excel 2002
Try VLOOKUP.
http://www.officearticles.com/excel/...soft_excel.htm ************ Anne Troy VBA Project Manager www.OfficeArticles.com "Darin Gibson" <Darin wrote in message ... I need to nest 10 IF functions. According to Excel help: Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the last of the following examples. What else can I do? 
#3




nested if functions in Excel 2002
Probably any number of things. However, to give you a specific alternative
requires that you state clearly what you are trying to accomplish "Darin Gibson" wrote: I need to nest 10 IF functions. According to Excel help: Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the last of the following examples. What else can I do? 
#4




nested if functions in Excel 2002
Hi Darin
With the limited info given, there is a way around this. Split your arguments over two comuns, the first testing the first say 6 ifs. As a result for If nr 6 being False, use "NOT DONE" Then in the 2nd column, test only conditions 7 to 10, in other words the ones stating "NOT DONE". With more info availabloe, VLOOKKUP could be an easier solution  ve_2nd_at. Stilfontein, Northwest, South Africa "Darin Gibson" wrote: I need to nest 10 IF functions. According to Excel help: Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the last of the following examples. What else can I do? 
#5




nested if functions in Excel 2002
Ok, here goes:
column F is the weight of each object. column h is the specific gravity of each object. In columns I,J,K,L,M,N,O,P, I want the weight of the object to be in the column in which it's specific gravity falls. here is the formula for each column (row 3 only) Col (range) formula I (<1.070) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,F3)))) ))) J (1.0701.075) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,F3,0)))) ))) K (1.0751.080) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,0,IF(H31.075,F3,IF(H31.07,0,0)))) ))) L (1.0801.085) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,F3,IF(H31.075,0,IF(H31.07,0,0)))) ))) M (1.0851.090) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,F3,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,0)))) ))) N (1.0901.095) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,F3,IF(H31.0 85,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,0)))) ))) O (1.0951.100) =IF(H31.1,0,IF(H31.095,F3,IF(H31.09,0,IF(H31.0 85,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,0)))) ))) P (1.100) =IF(H31.1,F3,IF(H31.095,0,IF(H31.09,0,IF(H31.0 85,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,0)))) ))) This worked great, but now I want to add 4 more categories (ie, change the 1.100 category to (1.1051.110) and also add (1.1101.115) , (1.1151.120) , and (1.120). For example, =IF(H31.12,F3,IF(H31.115,0,IF(H31.110,0,IF(H31 .105,0,IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H 31.085,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0, 0)))))))))) This is of course too many nested ifs. is there a way to use vlookup or index or match? Or am I better with split nesting? "Duke Carey" wrote: Probably any number of things. However, to give you a specific alternative requires that you state clearly what you are trying to accomplish "Darin Gibson" wrote: I need to nest 10 IF functions. According to Excel help: Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the last of the following examples. What else can I do? 
#6




nested if functions in Excel 2002
Taking the first of your formulas, in column I
=IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,F3)))) ))) All it really is doing is testing if it is between 1.075 and 1.07, right? =IF(AND(H3<=1.075,H31.07,f3,0) To make it more flexible, put each column's upper and lower limits in rows 1 and 2 (or whatever rows work in your sheet, but let's assume row 1 for the upper limit and row 2 for the lower limit for right now), let's modify the formula yet again: =IF(AND(H3<=I1,H3I2,f3,0) "Darin Gibson" wrote: Ok, here goes: column F is the weight of each object. column h is the specific gravity of each object. In columns I,J,K,L,M,N,O,P, I want the weight of the object to be in the column in which it's specific gravity falls. here is the formula for each column (row 3 only) Col (range) formula I (<1.070) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,F3)))) ))) J (1.0701.075) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,F3,0)))) ))) K (1.0751.080) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,0,IF(H31.075,F3,IF(H31.07,0,0)))) ))) L (1.0801.085) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,F3,IF(H31.075,0,IF(H31.07,0,0)))) ))) M (1.0851.090) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,F3,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,0)))) ))) N (1.0901.095) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,F3,IF(H31.0 85,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,0)))) ))) O (1.0951.100) =IF(H31.1,0,IF(H31.095,F3,IF(H31.09,0,IF(H31.0 85,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,0)))) ))) P (1.100) =IF(H31.1,F3,IF(H31.095,0,IF(H31.09,0,IF(H31.0 85,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,0)))) ))) This worked great, but now I want to add 4 more categories (ie, change the 1.100 category to (1.1051.110) and also add (1.1101.115) , (1.1151.120) , and (1.120). For example, =IF(H31.12,F3,IF(H31.115,0,IF(H31.110,0,IF(H31 .105,0,IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H 31.085,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0, 0)))))))))) This is of course too many nested ifs. is there a way to use vlookup or index or match? Or am I better with split nesting? "Duke Carey" wrote: Probably any number of things. However, to give you a specific alternative requires that you state clearly what you are trying to accomplish "Darin Gibson" wrote: I need to nest 10 IF functions. According to Excel help: Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the last of the following examples. What else can I do? 
#7




nested if functions in Excel 2002
Typo alert!!!
=IF(AND(H3<=1.075,H31.07),f3,0) =IF(AND(H3<=I1,H3I2),f3,0) "Duke Carey" wrote: Taking the first of your formulas, in column I =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,F3)))) ))) All it really is doing is testing if it is between 1.075 and 1.07, right? =IF(AND(H3<=1.075,H31.07,f3,0) To make it more flexible, put each column's upper and lower limits in rows 1 and 2 (or whatever rows work in your sheet, but let's assume row 1 for the upper limit and row 2 for the lower limit for right now), let's modify the formula yet again: =IF(AND(H3<=I1,H3I2,f3,0) "Darin Gibson" wrote: Ok, here goes: column F is the weight of each object. column h is the specific gravity of each object. In columns I,J,K,L,M,N,O,P, I want the weight of the object to be in the column in which it's specific gravity falls. here is the formula for each column (row 3 only) Col (range) formula I (<1.070) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,F3)))) ))) J (1.0701.075) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,F3,0)))) ))) K (1.0751.080) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,0,IF(H31.075,F3,IF(H31.07,0,0)))) ))) L (1.0801.085) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,F3,IF(H31.075,0,IF(H31.07,0,0)))) ))) M (1.0851.090) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,F3,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,0)))) ))) N (1.0901.095) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,F3,IF(H31.0 85,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,0)))) ))) O (1.0951.100) =IF(H31.1,0,IF(H31.095,F3,IF(H31.09,0,IF(H31.0 85,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,0)))) ))) P (1.100) =IF(H31.1,F3,IF(H31.095,0,IF(H31.09,0,IF(H31.0 85,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,0)))) ))) This worked great, but now I want to add 4 more categories (ie, change the 1.100 category to (1.1051.110) and also add (1.1101.115) , (1.1151.120) , and (1.120). For example, =IF(H31.12,F3,IF(H31.115,0,IF(H31.110,0,IF(H31 .105,0,IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H 31.085,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0, 0)))))))))) This is of course too many nested ifs. is there a way to use vlookup or index or match? Or am I better with split nesting? "Duke Carey" wrote: Probably any number of things. However, to give you a specific alternative requires that you state clearly what you are trying to accomplish "Darin Gibson" wrote: I need to nest 10 IF functions. According to Excel help: Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the last of the following examples. What else can I do? 
#8




nested if functions in Excel 2002
Excel 12 will suppport 64 nested functions
http://blogs.msdn.com/excel/archive/2005/09.aspx Until then, you have to stucture the calculation to work within current limits, as suggested by other responses. Jerry Darin Gibson wrote: I need to nest 10 IF functions. According to Excel help: Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the last of the following examples. What else can I do? 
#9




nested if functions in Excel 2002
Yes, this is much simpler, thanks.
Darin "Duke Carey" wrote: Typo alert!!! =IF(AND(H3<=1.075,H31.07),f3,0) =IF(AND(H3<=I1,H3I2),f3,0) "Duke Carey" wrote: Taking the first of your formulas, in column I =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,F3)))) ))) All it really is doing is testing if it is between 1.075 and 1.07, right? =IF(AND(H3<=1.075,H31.07,f3,0) To make it more flexible, put each column's upper and lower limits in rows 1 and 2 (or whatever rows work in your sheet, but let's assume row 1 for the upper limit and row 2 for the lower limit for right now), let's modify the formula yet again: =IF(AND(H3<=I1,H3I2,f3,0) "Darin Gibson" wrote: Ok, here goes: column F is the weight of each object. column h is the specific gravity of each object. In columns I,J,K,L,M,N,O,P, I want the weight of the object to be in the column in which it's specific gravity falls. here is the formula for each column (row 3 only) Col (range) formula I (<1.070) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,F3)))) ))) J (1.0701.075) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,F3,0)))) ))) K (1.0751.080) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,0,IF(H31.075,F3,IF(H31.07,0,0)))) ))) L (1.0801.085) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,0,IF(H31.08,F3,IF(H31.075,0,IF(H31.07,0,0)))) ))) M (1.0851.090) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H31.08 5,F3,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,0)))) ))) N (1.0901.095) =IF(H31.1,0,IF(H31.095,0,IF(H31.09,F3,IF(H31.0 85,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,0)))) ))) O (1.0951.100) =IF(H31.1,0,IF(H31.095,F3,IF(H31.09,0,IF(H31.0 85,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,0)))) ))) P (1.100) =IF(H31.1,F3,IF(H31.095,0,IF(H31.09,0,IF(H31.0 85,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0,0)))) ))) This worked great, but now I want to add 4 more categories (ie, change the 1.100 category to (1.1051.110) and also add (1.1101.115) , (1.1151.120) , and (1.120). For example, =IF(H31.12,F3,IF(H31.115,0,IF(H31.110,0,IF(H31 .105,0,IF(H31.1,0,IF(H31.095,0,IF(H31.09,0,IF(H 31.085,0,IF(H31.08,0,IF(H31.075,0,IF(H31.07,0, 0)))))))))) This is of course too many nested ifs. is there a way to use vlookup or index or match? Or am I better with split nesting? "Duke Carey" wrote: Probably any number of things. However, to give you a specific alternative requires that you state clearly what you are trying to accomplish "Darin Gibson" wrote: I need to nest 10 IF functions. According to Excel help: Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the last of the following examples. What else can I do? 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Excel 2002 calcs slower than Excel 97  Excel Discussion (Misc queries)  
how to convert GETPIVOTDATA from excel 2000 to excel 2002...  Excel Worksheet Functions  
Excel 2002 help  Excel Worksheet Functions  
Macro in Excel 2002 to save a workbook to a FTP location  Excel Discussion (Misc queries)  
Can you print labels using Excel 2002 in a Word 2002 mail merge?  Excel Discussion (Misc queries) 