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.