nested IF statements

[Harvey Waxman]

Is there a limit to the number of nested IF statements in a formula?
I'm sure there must be a more elegant way to get the data I need but for
now I'll try it this way.

 

[CyberTaz]

IIRC, IF()s can be nested up to 7 levels deep - but most of us cross the
bridge to insanity well before resolving one that comes anywhere near that
extent It's even worse to try analyzing a function where a multitude of
IF()s have been nested. Have a look at the other functions in the Logical
category such as AND(), OR(), & NOT() to more efficienly handle testing for
multiple conditions.

 

[Jim Gordon MVP]

Is there a limit to the number of nested IF statements in a formula?
I'm sure there must be a more elegant way to get the data I need but for
now I'll try it this way.

Hi,

There's no single answer to your question. It depends upon several
things, such as how many characters are in the IF and what other sorts
of things are in there. I've gotten up to 13 nests in a single cell.

The trick here is to realize that you can use the result of a one cell
in another cell's IF statement. The effect is that you can have
unlimited nesting if you split the conditions across more than one cell.

-Jim

 

[Harvey Waxman]

IIRC, IF()s can be nested up to 7 levels deep - but most of us cross the
bridge to insanity well before

Probably too late for me.

I'm trying to use data to develop a weighted average that I can use to
determine assessed property valuations using sales data instead of
revaluing each and every property.

I need only the total valuation not individual values so I might be able
to do it but I really don't understand SUMPRODUCT as well as I should.
I think this is the way to go.

If anyone wants to delve into this with me, I'm happy to respond. My
proposal is outlined at www.righttax.org and I'd like to help towns
avoid the annual revaluation expenses as part of the proposal.

Thanks for the reply

 

[Carl Witthoft]

Probably too late for me.

I'm trying to use data to develop a weighted average that I can use to
determine assessed property valuations using sales data instead of
revaluing each and every property.

I need only the total valuation not individual values so I might be able
to do it but I really don't understand SUMPRODUCT as well as I should.
I think this is the way to go.

If anyone wants to delve into this with me, I'm happy to respond. My
proposal is outlined at www.righttax.org and I'd like to help towns
avoid the annual revaluation expenses as part of the proposal.

Thanks for the reply

How about you post the logic you want, or even the current hyper-nested
IF statement here? Perhaps us web-geniuses can then suggest a
cleaner approach.

I would also recommend you play around with SUMPRODUCT, SUMIF, and
similar built-in functions to get accustomed to what they do. Following
that, we can get you going with array formulas, which basically will
allow you to do things like, e.g.,

=SUM( IF( [some_function_acting_on A1:A100]>D1100 ,B1:B100, C1:C100))

which examines each row in turn, selecting either the value in column B
or C depending on whether A is greater than D; then summing all 100
selections for you.

HTH

 

[Harvey Waxman]

How about you post the logic you want, or even the current hyper-nested
IF statement here? Perhaps us web-geniuses can then suggest a
cleaner approach.

I would also recommend you play around with SUMPRODUCT, SUMIF, and
similar built-in functions to get accustomed to what they do. Following
that, we can get you going with array formulas, which basically will
allow you to do things like, e.g.,

=SUM( IF( [some_function_acting_on A1:A100]>D1100 ,B1:B100, C1:C100))

which examines each row in turn, selecting either the value in column B
or C depending on whether A is greater than D; then summing all 100
selections for you.

HTH

It does. I'll play with it then post back once I can more clearly
articulate what I want to do.

And Thanks again to all