3 Part Formula: returning a "0" instead of blank if there is a formula in the cell

[syang]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]Course[/TD]
[TD]# Units[/TD]
[TD]# Units still needed[/TD]
[/TR]
[TR]
[TD]Math 1[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Math 2[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Math 5[/TD]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Math 3[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Math 4[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]Course[/TD]
[TD]# Units[/TD]
[TD]# Units still needed[/TD]
[/TR]
[TR]
[TD]Math 1[/TD]
[TD] =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=""),""))))))[/TD]
[TD] =IF(AND(B2>=3),"0",IF(AND(B2=""),"",SUM(3-B2)))[/TD]
[/TR]
[TR]
[TD]Math 2[/TD]
[TD] =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=""),""))))))[/TD]
[TD] =IF(AND(B3>=3),"0",IF(AND(B3=""),"",SUM(3-B3)))[/TD]
[/TR]
[TR]
[TD]Math 5[/TD]
[TD] =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=""),""))))))[/TD]
[TD] =IF(AND(B4>=3),"0",IF(AND(B4=""),"",SUM(3-B4)))[/TD]
[/TR]
[TR]
[TD]Math 3[/TD]
[TD] =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=""),""))))))[/TD]
[TD] =IF(AND(B5>="3"),"0",IF(AND(B5=""),"",SUM(3-B5)))[/TD]
[/TR]
[TR]
[TD]Math 4[/TD]
[TD] =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=""),""))))))[/TD]
[TD] =IF(AND(B6>="3"),"0",IF(AND(B6=""),"",SUM(3-B6)))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] =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=""),""))))))[/TD]
[TD] =IF(AND(B7>=3),"0",IF(AND(B7=""),"",SUM(3-B7)))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] =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=""),""))))))[/TD]
[TD] =IF(AND(B8>=3),"0",IF(AND(B8=""),"",SUM(3-B8)))[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[ChrisBM]

Just instead of "0" put "" and make sure the cells are not formatted as a number. General format should do nicely.

=IF(AND(B2>=3),"",IF(AND(B2=""),"",SUM(3-B2)))

 

[Scott Huish]

Why are you putting single conditions in AND?

But try this:
=IF(B2="","",MAX(3-B2,0))

For the rest of your formulas, you might want to look at the VLOOKUP function.

 

[jtakw]

I don't see a need for the AND & SUM

=IF(B2>=3,"",IF(B2="","",3-B2))

 

[syang]

Thank you, Scott Huish! Your formula worked GREAT...I really appreciate it.

ChrisBM and jtakw, thank you as well. I tried your formulas but the (3-B2) wasn't working; I was getting a BLANK when Column B was less than 3. Also, I do need a "0" just to show that something was calculated.

Have a great day, everyone!

 

[jtakw]

Thank you, Scott Huish! Your formula worked GREAT...I really appreciate it.

ChrisBM and jtakw, thank you as well. I tried your formulas but the (3-B2) wasn't working; I was getting a BLANK when Column B was less than 3. Also, I do need a "0" just to show that something was calculated.

Have a great day, everyone!

You're welcome,

but just to clarify, the reason 3-B2 didn't work is because your value in B2 is not a number but text, just so you know.

 

[jtakw]

Question for Scott...

I'm trying to learn and understand, since B2 is text, why does 3-B2 not work in my formula, but works in yours or even just plain =3-B2 ?

 

[jtakw]

OK, I think I figured out the problem or the cause...

My formula:
=IF(B2>=3,"",IF(B2="","",3-B2))

the first part, B2>=3, will always return TRUE; therefore, formula will always result in "", so the formula never gets to 3-B2,
B2>=3 is always TRUE because the real value of the text in B2 is greater than 3,
so to make my formula work, it needs to be:
=IF(B2>="3","",IF(B2="","",3-B2))

But...

Then why would 3-B2 work?...Why doesn't the same principle apply, e.g. 3 minus real value of text in B2?

 

[Scott Huish]

Why are you dealing with text "numeric" values anyway?

But as to why 3-B2 will work, is that if you do math on a text value, it will try and coerce it to a number.

 

[jtakw]

Why are you dealing with text "numeric" values anyway?

That's because the numeric value in B2 is text "3", "4", "5", "2" or "6", and not 3, 4, 5, 2, or 6, put there by OP's formula for Column B: =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=""),""))))))
The quotes for these numbers seems to be the culprit of the formulas not working, if these quotes are removed, even OP's original formula for Column C: =IF(AND(B2>=3),"0",IF(AND(B2=""),"",SUM(3-B2))) would have worked.

That's why my formula =IF(B2>=3,"",IF(B2="","",3-B2)) does not work,
and =IF(B2>="3","",IF(B2="","",3-B2)) does work.

But as to why 3-B2 will work, is that if you do math on a text value, it will try and coerce it to a number.

Thanks for the info, learned something new today.