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

LordCarl

New member
Joined
Nov 9, 2012
Messages
2
Reaction score
0
Points
0
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.
 
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.
 

Attachments

  • IM GETTING CLOSER.xlsx
    10.7 KB · Views: 13
Back
Top