Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Equation Efficiency?
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to improve efficiency of array formula | Excel Worksheet Functions | |||
How do I make a compressor map with efficiency islands? | Charts and Charting in Excel | |||
Calculation Efficiency (Speed)? | Excel Discussion (Misc queries) | |||
Range efficiency | Excel Worksheet Functions | |||
Question re efficiency in vlookup | Excel Worksheet Functions |