If Statement

  • Thread starter Thread starter Hank
  • Start date Start date
H

Hank

My worksheet the following columns
A DueDate
B DatePaid
C AmtPaid
D Overdue
E TotPaid

My if statement in Overdue (Col D) is: =IF(A1<TODAY(),15-C1," ")

The 15 is the amount that should have been paid ($15) from Col C. It work
fine except I would like for the cell to be blank rather than $0 if there is
nothing due.

What is the best way to do this. Or is there an easier way to obtain the
overdue amount?

Thanks
Hank
 
In Column D
=IF(AND(A1<TODAY(),15-C1=0),"",15-C1)
Overpayment shows up as negative value.
Now, if they get frisky and overpay (C1 has value larger than 15) and you
still don't want to see anything in D, then
=IF(AND(A1<TODAY(),15-C1<=0),"",15-C1)
 
One way (but this could affect dependent formulae performing arithmetic
operations on your results):
=IF(AND(C1<>15,A1<TODAY()),15-C1,"")

Or, you could use a custom number format, which would retain a 0 value in
the cell, but would not display it. For example
$#,###.00_);($#,###.00);;

Or you can have excel suppress all zeroes by
Tools/Options/View - uncheck zero values.
 
Back
Top