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:

Some videos you may like

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
Joined
Sep 22, 2014
Messages
215
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
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Jun 29, 2014
Messages
5,146
I don't see a need for the AND & SUM

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

syang

New Member
Joined
Sep 21, 2013
Messages
21

ADVERTISEMENT

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
Joined
Jun 29, 2014
Messages
5,146
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
Joined
Jun 29, 2014
Messages
5,146

ADVERTISEMENT

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
Joined
Jun 29, 2014
Messages
5,146
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
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Jun 29, 2014
Messages
5,146
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,755
Messages
5,524,670
Members
409,597
Latest member
Dannydev

This Week's Hot Topics

Top