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!

 

[Scott Huish]

It would be easier to modify the formulas to use actual numbers, although I would do it via a LOOKUP table

@syang:
It is better if you don't put numbers in quotes, because that makes them text.

 

[syang]

It would be easier to modify the formulas to use actual numbers, although I would do it via a LOOKUP table

@syang:
It is better if you don't put numbers in quotes, because that makes them text.

Interesting...I've played around with quotes and no quotes around my numbers and it changes the outcome in my cell. I never knew why, so thanks for the tip!

 

[jtakw]

Interesting...I've played around with quotes and no quotes around my numbers and it changes the outcome in my cell. I never knew why, so thanks for the tip!

Read my posts #8 & #10 for a description of the problem, your original formula in column C (although not the most efficient) would have worked if the quotes weren't in your formula in column B. So for future reference, don't put quotes around a number if you want it treated as a real number.

A way to test any cell containing a number if it is actually a REAL number:
In any unused cell, put =ISNUMBER(Input cell reference), e.g. =ISNUMBER(B2), it will return TRUE or FALSE.

 

[syang]

Read my posts #8 & #10 for a description of the problem, your original formula in column C (although not the most efficient) would have worked if the quotes weren't in your formula in column B. So for future reference, don't put quotes around a number if you want it treated as a real number.

A way to test any cell containing a number if it is actually a REAL number:
In any unused cell, put =ISNUMBER(Input cell reference), e.g. =ISNUMBER(B2), it will return TRUE or FALSE.

For the purpose of learning and helping others, I decided to test out the tips in the previous posts and made a strange discovery: in my post #1, I didn’t even realize that Math 3 and Math 4 had a different formula in Column C! There are quotes around the first “3.” I may have done this when I was playing around with the formulas to get them to work.

This formula with the quotes around the first 3: =IF(AND(B2>="3"),"0",IF(AND(B2=""),"",SUM(3-B2)))
actually does everything I need: returns 0 if B is more than/equal to 3, returns blank if there is no value in B, and the (3-B2) part works as well. It works without taking the quotes away from the numbers in my column B formula.

I ran 2 tests:

Column B: =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=""),""))))))

Column C: =IF(AND(B2>="3"),"0",IF(AND(B2=""),"",SUM(3-B2)))

=ISNUMBER(B2) returns FALSE

My findings: If there are quotes around ALL numbers in both formulas, then it all works out. Notice that rows 7 & 8 are all blank.

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


Column B: =IF(AND(A9="Math 1"),3,IF(AND(A9="Math 2"),4,IF(AND(A9="Math 3"),5,IF(AND(A9="Math 4"),2,IF(AND(A9="Math 5"),6,IF(AND(A9=""),""))))))

Column C: =IF(AND(B9>=3),"0",IF(AND(B9=""),"",SUM(3-B9)))

=ISNUMBER(B9) returns TRUE

My findings: @jtakw, referring to your post #13, I took out the quotes around all my numbers in Column B and used my original formula in Column C, but rows 14 & 15 return a 0 in Column C instead of a blank. So for what I needed, this method didn't work.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Course[/TD]
[TD]# Units[/TD]
[TD]# Units Still Needed[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Math 1[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Math 2[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Math 3[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Math 4[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Math 5[/TD]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


This has been a great learning session, and I agree that my formula is more complex than needed. I will be using Scott's formula. Thanks again everyone for all your input!

 

[jtakw]

syang,

I don't want to leave you half understanding the difference between TEXT and REAL values.

My findings: @jtakw, referring to your post #13, I took out the quotes around all my numbers in Column B and used my original formula in Column C, but rows 14 & 15 return a 0 in Column C instead of a blank. So for what I needed, this method didn't work.

This is because your REAL value in B14 & B15 is NOT blank, the blank was put there by your formula in Column B using "", this makes it TEXT. In an unused cell, put =ISBLANK(B14), and it wll return FALSE, meaning there's something there, you just can't see it.

And also, your zeros in column C are not zeros, they are TEXT, because you used "0" in your formula in both tests.
Column C: =IF(AND(B2>="3"),"0",IF(AND(B2=""),"",SUM(3-B2)))
Column C: =IF(AND(B9>=3),"0",IF(AND(B9=""),"",SUM(3-B9)))

In unused cells put =ISNUMBER(C2), and =ISNUMBER(C9), and they will return FALSE

For your future formulas, don't use quotes around any number value if you will be referring back to that cell for another calculation.
Basically, if there are quotes in a formula, it becomes TEXT, even if there is nothing between the quotes, e.g. ""

 

[syang]

syang,

I don't want to leave you half understanding the difference between TEXT and REAL values.



This is because your REAL value in B14 & B15 is NOT blank, the blank was put there by your formula in Column B using "", this makes it TEXT. In an unused cell, put =ISBLANK(B14), and it wll return FALSE, meaning there's something there, you just can't see it.

And also, your zeros in column C are not zeros, they are TEXT, because you used "0" in your formula in both tests.
Column C: =IF(AND(B2>="3"),"0",IF(AND(B2=""),"",SUM(3-B2)))
Column C: =IF(AND(B9>=3),"0",IF(AND(B9=""),"",SUM(3-B9)))

In unused cells put =ISNUMBER(C2), and =ISNUMBER(C9), and they will return FALSE

For your future formulas, don't use quotes around any number value if you will be referring back to that cell for another calculation.
Basically, if there are quotes in a formula, it becomes TEXT, even if there is nothing between the quotes, e.g. ""

I will have to remember TEXT vs REAL values. I can see why B14 & B15 are considered REAL since [=ISNUMBER(B9)] returns TRUE to tell me that it's REAL, not TEXT.

I suppose that's why the formulas in B and C work when there are quotes around ALL the numbers. Everything is consistently searching for TEXT and not a combination of TEXT and REAL values. The [IF(AND(B2=""),""] part of the formula successfully returns BLANK because B is TEXT.

I hope I got that right

 

[Scott Huish]

There's no need to put AND around a single condition.

=IF(B2="","",what to do if false)

Also instead of those long nested IFs, just use VLOOKUP. Make a table like I did in H1:I5 and then it would be like this:

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Math 1</td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Math 2</td><td style="text-align:right; ">4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Math 2</td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Math 3</td><td style="text-align:right; ">5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Math 4</td><td style="text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Math 5</td><td style="text-align:right; ">6</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=IF(A2="","",VLOOKUP<span style=' color:008000; '>(A2,$H$1:$I$5,2,0)</span>)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

 

[syang]

[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #CACACA"]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[TD]=IF(A2="","",VLOOKUP(A2,$H$1:$I$5,2,0))[/TD]
[/TR]
</tbody>[/TABLE]


Oh wow! This saves me a huge headache and totally works! Plus, I already have tables like H1:I5 on Sheet 2. Thank you SOOOO much!