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)))

=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: