Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Nov 2007
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    calculating nested IF(AND sequence (Excel 2003)

    hi there
    in cell T12 i need to calculate the unit grade result (fail,pass,merit,distinction) which i have shotrtened to F, P, M, D based on the results in B12,C12,D12 and whether the result in E12 was a pass or fail and I12, J12, K12, L12, M12, N12, O12 and whether the reuslt in Q12 was a pass or fail, this result is based on the fact that they must have passed on all the assignments and the questions, failure to do so means a fail overall.

    i have fiddled all day long with this and have gave up, the closest function i can think of is
    =IF(AND(E12="Pass",Q12="Pass"),IF(AVERAGE(B12,C12, D12,I12, J12, K12, L12, M12, N12, O12)>75,"Distinction",IF(AVERAGE(B12,C12,D12,I12, J12, K12, L12, M12, N12, O12)>60,"Merit","Pass")),"Fail")
    this function is daft i know, and doesnt work as tested it by changed some of the results to force a distinction with no success. is there a better way of doing it, one that works, and can you tell me how and why so i can understand?
    regards kitty
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating nested IF(AND sequence (Excel 2003)

    Do you really want to calculate the overall average of B12, C12, D12, I12, J12, K12, L12, M12, N12, O12? The numbers in B1212 are on a different scale than those in I12:O12. It would seem more logical to look at the score in S12.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating nested IF(AND sequence (Excel 2003)

    Let's assume that it is acceptable to use column S to compute the grade.
    You were already given several methods for making the calculation simpler in your first thread (starting at <post:=674,491>post 674,491</post:>) and again in the thread starting at <post:=676,105>post 676,105</post:>.

    The attached workbook demonstrates its use. The formula in T12 is

    =IF(AND(E12="Pass",Q12="Pass"),INDEX($K$2:$N$2,MAT CH(S12,$K$4:$N$4)),"Fail")

    and this can be filled down.
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Nov 2007
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating nested IF(AND sequence (Excel 2003)

    this value is not showing as correct see print screen cpoy attachment, i need both passes and the results to find the true unit grade result in cell T12
    a value is not available to the formula or function?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating nested IF(AND sequence (Excel 2003)

    Attachment? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    Star Lounger
    Join Date
    Nov 2007
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating nested IF(AND sequence (Excel 2003)

    attachment
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating nested IF(AND sequence (Excel 2003)

    Please take another look at the version I attached higher up in this thread. I changed the values in cells K4:N4 to enable using the MATCH function (as indicated in the earlier threads). The cells contain the values 0, 40, 60 and 75, but I applied a custom number format to display 0+, 40+ etc.

Posting Permissions

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