Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    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

  2. #2
    Plutonium Lounger
    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.

  3. #3
    Uranium Lounger
    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 the
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Uranium Lounger
    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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    Uranium Lounger
    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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    2 Star Lounger
    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

  9. #9
    2 Star Lounger
    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

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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

Posting Permissions

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