Results 1 to 2 of 2

Thread: Complex combination of conditions in one cell, please help :o(

  1. #1

    Question Complex combination of conditions in one cell, please help :o(



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello Excel lords, I know a little about excel and usually can formulate anything I need, but this is getting me crazy so please help me.

    I am a counselor at a school and I was trying to formulate a spreadsheet that calculate the total number of “credits” that an student had completed based on different courses so here is my problem and the example:

    Most of courses had 2 parts, A and B, and each part worth “0.5” of credit or half of credit, but my problems comes when I realize that I have 6 different possibilities or combinations and I need that my formula give me 3 possible values and only one of those 3 values, 0 or 0.5 or 1 depends on what combination I have.

    Here are all the possibilities I can have based on 2 cells, Part A & B

    The six possible conditions…
    Part A Part B
    1) 70 or more / 69 or less = if average is greater or equal to 70 result should be 1 one
    2) 70 or more / 69 or less = if average is less or equal to 69 result should be 0.5 point five
    3) 69 or less / 70 or more = if average is greater or equal to 70 result should be 1 one
    4) 69 or less / 70 or more = if average is less or equal to 69 result should be 0.5 point five
    5) 69 or less / 69 or less = if average is less or equal to 69 result should be 0 zero
    6) 70 or more / 70 or more if average is greater or equal to 70 result should be 1 one

    3 Will lead to result = 1
    2 will lead to result = 0.5
    1 will lead to result = 0

    Please i beg someone can help me because this is will save me hours and hours of work.

    Thanks in advance
    Carl.

  2. #2

    Post Final problem

    OK I figure out the tiny formula... supposing that column A and Column B are in use in row 3

    =IF(AND(OR(A3>=70,B3<=69),AVERAGE(A3:B3)>=69.5),1,IF(AND(OR(A3>=70,B3<=69),AVERAGE(A3:B3)<=69.4),0.5,IF(AND(OR(A3<=69,B3>=70),AVERAGE(A3:B3)>=69.5),1,IF(AND(OR(A3<=69,B3>=70),AVERAGE(A3:B3)<=69.4),0.5,IF(AND(OR(A3<=69,B3<=69),AVERAGE(A3:B3)<=69),0,IF(AND(OR(A3>=70,B3>=70),AVERAGE(A3:B3)>=69.5),1,0))))))

    Here is attahced my spreadsheet with this same formula using all possibilities but theres one that fail to give me the correct result and this is when I have 2 failing grades please advise why? , im getting crazy!!!!

    Thank you very much.
    Attached Files Attached Files

Posting Permissions

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