How can I debug my complex IF Excel formula?

问题:

I am a school teacher and am developing my school's assessment system. What should I do to correct this formula?

=IF(H3="SUR",IF(Subject!C3<2.7,"Not on track",IF(Subject!C3<=3.29,"On track",IF(Subject!C3>=3.3,"Above", IF(H3="MAS",IF(Subject!C3<2.2,"Not on track",IF(Subject!C3<=2.79,"On track",IF(Subject!C3>=2.8,"Above", IF(H3="EXP",IF(Subject!C3<1.7,"Not on track",IF(Subject!C3<=2.29,"On track",IF(Subject!C3>=2.3,"Above",IF(H3="EM3",IF(Subject!C3<1.2,"Not on track",IF(Subject!C3<=1.79,"On track",IF(Subject!C3>=1.8,"Above", IF(H3="EM2",IF(Subject!C3<0.7,"Not on track",IF(Subject!C3<=1.29,"On track",IF(Subject!C3>=1.3,"Above",IF(H3="EM1",IF(Subject!C3<0.23,"Not on track",IF(Subject!C3<=0.42,"On track",IF(Subject!C3>=0.43,"Above"))))))))))))))))))))))))

The result is dependent upon what is entered within H_, and each category has specific ranges.

回答1:

As was said in the comments, create a table:

Then you would use the following Array formula:

=INDEX(O:O,MATCH(C3,IF($M$1:$M$18=H3,$N$1:$N$18)))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly Excel will put {} around the formula.

Each group needs to be together and in ascending order for this method to work.

It does allow the change of criteria and output without changing the hard coded formula.

回答2:

It should be two separate lookups, because the text lookup is exact and the number lookup is approximate. If for example if you have this in the range A1:D6 :

EM1     0.23    0.43    Exp
EM2     0.7     1.3     2
EM3     1.2     1.8
EXP     1.7     2.3
MAS     2.2     2.8
SUR     2.7     3.3

Then =VLOOKUP("EXP",A1:C6,2,0) results in 1.7 and =VLOOKUP("EXP",A1:C6,3,0) in 2.3

=IF(D2 < VLOOKUP(D1, A1:C6, 2, 0), "Not on track",
 IF(D2 < VLOOKUP(D1, A1:C6, 3, 0), "On track", "Above"))

回答3:

Here is how to help yourself in 3 steps.

Step 1 - Enlarge the formula bar so it contains more than 1 row: Step 2 - Use Alt+Enter to make the formula understandable: Step 3 - On the Excel ribbon, go to Formulas > Evaluate Formula and press evaluate:

Good luck, have fun! :)