In trying to create "validated" spreadsheets for my company that meet their
rigorous criteria I frequently run into the problem of wanting more than
seven IF statements in a single equation. Here's an example where I've
reached the seven statement limit but need one mo

=IF(AND(F31="N/A",F32="N/A"),"N/A",IF(AND(F31="ND",F32="ND"),"ND",IF(OR(F31="Te xt
Error",F32="Text Error"),"Text Error",IF(OR(F31="Numeric Error",F32="Numeric
Error"),"Numeric Error",IF(F31="ND", F32,
IF(AND(F310.1049,F32<=0.1049),F31,IF(AND(F320.10 49,F31<=0.1049),F32,AVERAGE(F31:F32))))))))

I'm sure there's a way to be more efficient with the given criteria but I'm
not fluent enough in excel equations to shorten it. Given enough time I have
shortened these in the past but it's not pretty. Are there any tips or
tricks that could help me be more efficient with my equations?

Criteria to meet for a simple AVERAGE calculation:
if both cells are "N/A" return "N/A"
if both cells are "ND" return "ND"
if a text value is entered other than "ND" return "Text Error"
if a negative value is entered return "Numerical Error"
if one cell is "ND" return the other (this usually takes two IF statements
for me)
if the calculated value from one cell is greater than the specified limit
and one is less, don't average and return the high value
if both cells are above the limit, average as normal

Two suggestions:

1. If upgrading to Excel 2007 is a possibility, go for it. It will allow
64 nested Ifs. That should accomodate the most prolific if'er.

2. In my pre-2007 days, I would evaluate criteria in seperate columns. If
the result was True, I would assign a value of 1. 0 if False. Then I would
use a final If to perform the final calculation based on the individual
columns. =IF(AND(AA1=0,AB1=1,AC1=1... etc.

Your formula looks to be fairly compact as it is. If your problem is an
'If' limitation, then there is not a great deal that I know of. Hopefully
someone else can provide a better answer.

Finally, you might want to look into VBA for your answer. You can do some
pretty wild stuff if you can learn it or hire someone already familiar.
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"Ithaca" wrote:

In trying to create "validated" spreadsheets for my company that meet their
rigorous criteria I frequently run into the problem of wanting more than
seven IF statements in a single equation. Here's an example where I've
reached the seven statement limit but need one mo

=IF(AND(F31="N/A",F32="N/A"),"N/A",IF(AND(F31="ND",F32="ND"),"ND",IF(OR(F31="Te xt
Error",F32="Text Error"),"Text Error",IF(OR(F31="Numeric Error",F32="Numeric
Error"),"Numeric Error",IF(F31="ND", F32,
IF(AND(F310.1049,F32<=0.1049),F31,IF(AND(F320.10 49,F31<=0.1049),F32,AVERAGE(F31:F32))))))))

I'm sure there's a way to be more efficient with the given criteria but I'm
not fluent enough in excel equations to shorten it. Given enough time I have
shortened these in the past but it's not pretty. Are there any tips or
tricks that could help me be more efficient with my equations?

Criteria to meet for a simple AVERAGE calculation:
if both cells are "N/A" return "N/A"
if both cells are "ND" return "ND"
if a text value is entered other than "ND" return "Text Error"
if a negative value is entered return "Numerical Error"
if one cell is "ND" return the other (this usually takes two IF statements
for me)
if the calculated value from one cell is greater than the specified limit
and one is less, don't average and return the high value
if both cells are above the limit, average as normal