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? |
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? |
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? |
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? |
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.070-1.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.075-1.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.080-1.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.085-1.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.090-1.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.095-1.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.105-1.110) and also add (1.110-1.115) , (1.115-1.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? |
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.070-1.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.075-1.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.080-1.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.085-1.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.090-1.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.095-1.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.105-1.110) and also add (1.110-1.115) , (1.115-1.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? |
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.070-1.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.075-1.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.080-1.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.085-1.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.090-1.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.095-1.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.105-1.110) and also add (1.110-1.115) , (1.115-1.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? |
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? |
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.070-1.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.075-1.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.080-1.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.085-1.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.090-1.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.095-1.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.105-1.110) and also add (1.110-1.115) , (1.115-1.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? |
All times are GMT +1. The time now is 05:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com