Column A | Column B | Column C |
Course | # Units | # Units still needed |
Math 1 | 3 | 0 |
Math 2 | 4 | 0 |
Math 5 | 6 | 0 |
Math 3 | 5 | 0 |
Math 4 | 2 | 1 |
0 | ||
0 |
<tbody>
</tbody>
I would like a formula in Column C to do 3 things: return "0" if column B is 3 or more, return a blank cell if there is no value in Column B, and if Column B is less than 3, I want the difference of 3 minus Column B.
The formula in I came up with:
=IF(AND(B2>=3),"0",IF(AND(B2=""),"",SUM(3-B2)))
The red part of the formula is NOT working. I want Column C to be blank if Column B to the left of it is blank, but it's showing up as 0 instead. There IS a formula in Column B which might be preventing this part from working.
I have a data set where if a student takes a Math course that is 3 or more units, they fulfill the requirement and Column C will say 0. This is the green part of the formula, which is working fine because all numbers in Column B that is greater than/equal to 3 shows up as a 0 in Column C.
If the course is less than 3 units, I want the formula to automatically calculate how many units they still need in Column C. This is the blue part of the formula, which is working fine because Math 4 (2 units) shows up as still needing 1 unit in Column C.
If it helps, here is the data set with the visible formulas:
Column A | Column B | Column C |
Course | # Units | # Units still needed |
Math 1 | =IF(AND(A2="Math 1"),"3",IF(AND(A2="Math 2"),"4",IF(AND(A2="Math 3"),"5",IF(AND(A2="Math 4"),"2",IF(AND(A2="Math 5"),"6",IF(AND(A2=""),"")))))) | =IF(AND(B2>=3),"0",IF(AND(B2=""),"",SUM(3-B2))) |
Math 2 | =IF(AND(A3="Math 1"),"3",IF(AND(A3="Math 2"),"4",IF(AND(A3="Math 3"),"5",IF(AND(A3="Math 4"),"2",IF(AND(A3="Math 5"),"6",IF(AND(A3=""),"")))))) | =IF(AND(B3>=3),"0",IF(AND(B3=""),"",SUM(3-B3))) |
Math 5 | =IF(AND(A4="Math 1"),"3",IF(AND(A4="Math 2"),"4",IF(AND(A4="Math 3"),"5",IF(AND(A4="Math 4"),"2",IF(AND(A4="Math 5"),"6",IF(AND(A4=""),"")))))) | =IF(AND(B4>=3),"0",IF(AND(B4=""),"",SUM(3-B4))) |
Math 3 | =IF(AND(A5="Math 1"),"3",IF(AND(A5="Math 2"),"4",IF(AND(A5="Math 3"),"5",IF(AND(A5="Math 4"),"2",IF(AND(A5="Math 5"),"6",IF(AND(A5=""),"")))))) | =IF(AND(B5>="3"),"0",IF(AND(B5=""),"",SUM(3-B5))) |
Math 4 | =IF(AND(A6="Math 1"),"3",IF(AND(A6="Math 2"),"4",IF(AND(A6="Math 3"),"5",IF(AND(A6="Math 4"),"2",IF(AND(A6="Math 5"),"6",IF(AND(A6=""),"")))))) | =IF(AND(B6>="3"),"0",IF(AND(B6=""),"",SUM(3-B6))) |
=IF(AND(A7="Math 1"),"3",IF(AND(A7="Math 2"),"4",IF(AND(A7="Math 3"),"5",IF(AND(A7="Math 4"),"2",IF(AND(A7="Math 5"),"6",IF(AND(A7=""),"")))))) | =IF(AND(B7>=3),"0",IF(AND(B7=""),"",SUM(3-B7))) | |
=IF(AND(A8="Math 1"),"3",IF(AND(A8="Math 2"),"4",IF(AND(A8="Math 3"),"5",IF(AND(A8="Math 4"),"2",IF(AND(A8="Math 5"),"6",IF(AND(A8=""),"")))))) | =IF(AND(B8>=3),"0",IF(AND(B8=""),"",SUM(3-B8))) |
<tbody>
</tbody>
How can I make the formula return a BLANK cell rather than a "0"? I've always been able to make it work, but for some reason, it's not working. I really appreciate the help! Thank you in advance!
Last edited: