Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 18th 05, 05:42 PM posted to microsoft.public.excel.worksheet.functions
Darin Gibson
 
Posts: n/a
Default 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?

  #3   Report Post  
Old November 18th 05, 06:06 PM posted to microsoft.public.excel.worksheet.functions
Duke Carey
 
Posts: n/a
Default 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   Report Post  
Old November 18th 05, 08:09 PM posted to microsoft.public.excel.worksheet.functions
Kassie
 
Posts: n/a
Default 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   Report Post  
Old November 18th 05, 08:57 PM posted to microsoft.public.excel.worksheet.functions
Darin Gibson
 
Posts: n/a
Default 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?



  #6   Report Post  
Old November 18th 05, 09:29 PM posted to microsoft.public.excel.worksheet.functions
Duke Carey
 
Posts: n/a
Default 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?

  #7   Report Post  
Old November 18th 05, 09:33 PM posted to microsoft.public.excel.worksheet.functions
Duke Carey
 
Posts: n/a
Default 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?

  #8   Report Post  
Old November 19th 05, 11:04 AM posted to microsoft.public.excel.worksheet.functions
Jerry W. Lewis
 
Posts: n/a
Default 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   Report Post  
Old November 22nd 05, 08:51 PM posted to microsoft.public.excel.worksheet.functions
Darin Gibson
 
Posts: n/a
Default 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?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2002 calcs slower than Excel 97 David Excel Discussion (Misc queries) 0 March 24th 05 04:23 PM
how to convert GETPIVOTDATA from excel 2000 to excel 2002... Need_help_on_excel Excel Worksheet Functions 1 March 15th 05 02:08 AM
Excel 2002 help Rob Excel Worksheet Functions 1 January 13th 05 12:26 AM
Macro in Excel 2002 to save a workbook to a FTP location Lloyd Excel Discussion (Misc queries) 0 December 21st 04 03:49 PM
Can you print labels using Excel 2002 in a Word 2002 mail merge? Individual_ Excel Discussion (Misc queries) 3 December 17th 04 09:39 PM


All times are GMT +1. The time now is 09:00 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017