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

syang

New Member
Joined
Sep 21, 2013
Messages
21
Column AColumn BColumn C
Course# Units# Units still needed
Math 130
Math 240
Math 560
Math 350
Math 421
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 AColumn BColumn 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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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)))
 
Upvote 0
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.
 
Upvote 0
I don't see a need for the AND & SUM

=IF(B2>=3,"",IF(B2="","",3-B2))
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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 ?
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top