ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   nested if functions in Excel 2002 (https://www.excelbanter.com/excel-worksheet-functions/56197-nested-if-functions-excel-2002-a.html)

Darin Gibson

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?

Anne Troy

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?




Duke Carey

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?


Kassie

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?


Darin Gibson

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?


Duke Carey

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?


Duke Carey

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?


Jerry W. Lewis

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?



Darin Gibson

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