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

#### syang

##### New Member
 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:

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### ChrisBM

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

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

#### Scott Huish

##### MrExcel MVP
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

##### Well-known Member
I don't see a need for the AND & SUM

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

#### syang

##### New Member

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!

Last edited:

#### jtakw

##### Well-known Member
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

##### Well-known Member

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

##### Well-known Member
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?

Last edited:

#### Scott Huish

##### MrExcel MVP
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

##### Well-known Member
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.

Replies
14
Views
123
Replies
6
Views
51
Replies
18
Views
216
Replies
1
Views
35
Replies
3
Views
221