Results 1 to 10 of 10

20030601, 21:37 #1
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 168
 Thanks
 1
 Thanked 0 Times in 0 Posts
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

20030601, 21:55 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20030601, 22:20 #3
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
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 theJohn ... I float in liquid gardens
UTC 7ąDS

20030601, 22:32 #4
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
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" ... ?John ... I float in liquid gardens
UTC 7ąDS

20030601, 23:44 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20030602, 01:21 #6
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
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.John ... I float in liquid gardens
UTC 7ąDS

20030602, 10:13 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20030603, 04:05 #8
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 168
 Thanks
 1
 Thanked 0 Times in 0 Posts
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

20030603, 04:07 #9
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 168
 Thanks
 1
 Thanked 0 Times in 0 Posts
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

20030603, 10:34 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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