# Thread: nested if then formula (office 2000)

1. ## nested if then formula (office 2000)

Hello All in the know,
I am attaching a file that has my dilemma spelled out. I know there is got to be a way to write this nested sum results, if then, formula, however it is just a little beyond me. Any and all help is greatly appreciated. If any one has any better solutions ... let me know.
tia
Bill

2. ## Re: nested if then formula (office 2000)

Does the attached modified workbook help? I put array formulas (confirmed with Ctrl+Shift+Enter) in I20:N20, and used the results in G20.

3. ## Re: nested if then formula (office 2000)

In cell G20, test

=CHOOSE(1+((COUNTIF(I17:N19,"M")+COUNTIF(I17:N19," E"))>=8)+((COUNTIF(I17:N19,"M")+COUNTIF(I17:N19,"E "))>=12),"D","M","E")

and see if it works for you. The formula doesn't include the

4. ## Re: nested if then formula (office 2000)

After a further look, it appears the the "within 20%" rule doesn't apply to the table in I17:N19? For instance in column J, your results show "D", but I would calculate
14%/17% = 80%, which would be an "M" ... ?

5. ## Re: nested if then formula (office 2000)

I would also think that K19 should be an "E" not an "M" since 63/50 = 125% and that L17 should be an "M" and not an "E" since 73/62 = 118%.

Either I don't understand where these numbers come from or <80% is "D" = 0, and >120% = "E" = 2 and all else is = "M" = 1 is not a hard and fast rule.

If I understand the question, I think in I17 you want:
=IF(+I\$9/I11<0.8,"D",IF(+I\$9/I11>1.2,"E","M"))
This can be copied into I17:L19

In N17:
=IF(+O\$9/N11<0.8,"D",IF(+O\$9/N11>1.2,"E","M"))
Copy this to N17:N19

Then in G20:
=IF((COUNTIF(I17:N19,"M") +2*COUNTIF(I17:N19,"E"))/15<0.8,"D",IF((COUNTIF(I17:N19,"M") +2*COUNTIF(I17:N19,"E"))/15>1.2,"E","M"))

Steve

6. ## Re: nested if then formula (office 2000)

Agreed on the I17:N19 formulas.

And in the G20 formula, /15<0.8 can be simplified to <12, and /15>=1.2 to >=18.

7. ## Re: nested if then formula (office 2000)

Yes that is simpler, I was trying to keep the "technique" and calcs more apparent.

An even simpler method (but perhaps LESS intuitive) is in I17:
<pre>=+I\$9/I11</pre>

copied to i17:L19

Then in N17:
<pre>=+O\$9/N11</pre>

copied to N17:N19

Format I17:N19 using the custom format:
[<0.8]"D";[>1.2]"E";"M"

Then in G20 (also formatted as above):
<pre>=(COUNTIF(I17:N19,">=0.8")+COUNTIF(I17:N19,"> 1.2"))/15</pre>

This gets the same results as before, but you keep the values and only DISPLAY the "GRADE"

Though you could also use in G20 (againi, with cusom format)
<pre>=average(I17:N19)</pre>

If you are more concerned about the aveage SCORE than the average "GRADE", though the results will NOT always be the same. YOu could also weight the average to "score" of the region/company/goal comparisons differently if desired.

Steve

8. ## Re: nested if then formula (office 2000)

Thanks to all who have contributed to this sheet so far,
This is right on track with what I need to do, and you were right the formula <.8 and >1.2 needed to be applied at all aspects of the calcs... my oversight. Thanks for pointing this out.
Just one question,
Why is it that when I change the cells in I17:n20 to the formulas suggested the array if then formula in row 20 --- i through n does not calculate. If i put the letters in the cells i17 : n20 then the array if formula works?
Am I just an idiot .... don't answer that...[img]/forums/images/smilies/smile.gif[/img] .... or did I miss something?
tia
Bill

9. ## Re: nested if then formula (office 2000)

to all again,
I got to get some sleep I forgot to attach the file..... sorry
again, tia,
Bill

10. ## Re: nested if then formula (office 2000)

By doing the custom formatting, the cells do NOT contain D, M, or E they contain the VALUE 0.7, 1.2, etc.

Then in G20 (formatted: [<0.8]"D";[>1.2]"E";"M"):
<pre>=(COUNTIF(I17:N19,">=0.8")+COUNTIF(I17:N19,"> 1.2"))/15</pre>

You do NOT need the arrays in I20:N20 as the above calculates it directly and will display as D, M, E. If you don't want the "Grade" displayed, just format the cell as something else (General, 0.0, 0%, or whatever), the cell has the conversion of [(count M) + 2*(Count E)]/15 to a number. The custom format just converts to grade.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•