Get rid of #VALUE!

[sxhenry20]

I have four columns. B1 is amount owed. C1 is amount paid. D1 is the difference and E1 is the remainder. Pretty simple. I have this set up for the next six years on a monthly basis so all i want to enter is the "paid" amount. My formulas figure out the difference.
In my remainder column, I need it to take the difference and the remainder from the previous month to add it together. I have the formula:

=IF(OR(ISBLANK(E3),ISBLANK(D4)),NA(),E3+D4)

The problem is, I can't figure out how to to get rid of the #VALUE! without getting rid of the formula. What do I need to add, and where, to get rid of this darn thing?

 

[Haz]

You're checking if D4 is empty, but it's not. D4 is ""

Change ISBLANK(D4) to D4=""
Do the same in the J column

 

[sxhenry20]

I did that, but now it says #N/A in the cells.
=IF(OR(ISBLANK(E4),D5=""),NA(),E4+D5)
I know I have to get rid of the NA(), but not sure exactly how to write the formula. if I just remove the NA(), the #Value! shows up again.

 

[Haz]

Change all the ISBLANK() to =""
Change all the NA() to ""

 

[Peter Bartholomew]

It is somewhat annoying that ISBLANK returns FALSE whenever the referenced cell contains a formula, even though the formula may evaluate to give "".

Another option to the one suggested be reverse the conditions from a negative test
=IF(OR(ISBLANK(value1), ISBLANK(value2)),"",calculation)
to a positive test

=IF(AND(ISNUMBER(value1), ISNUMBER(value2)),calculation,"")
​

 

[sxhenry20]

Change all the ISBLANK() to =""
Change all the NA() to ""

Thank you for the recommendation, however when I did that, Excel didn't even recognize that as a proper formula.

 

[sxhenry20]

It is somewhat annoying that ISBLANK returns FALSE whenever the referenced cell contains a formula, even though the formula may evaluate to give "".

Another option to the one suggested be reverse the conditions from a negative test
=IF(OR(ISBLANK(value1), ISBLANK(value2)),"",calculation)
to a positive test

=IF(AND(ISNUMBER(value1), ISNUMBER(value2)),calculation,"")
​

I think I am about to give up and deal with just seeing the #N/A in the field. When I tried your suggestion, it actually deleted the formula out of the cell and just its own formula (i.e. =D4) as the formula. And then when I put an amount into the Paid column to see if it figured correctly, I got #NAME in the cell.
I do appreciate the suggestion though.

 

[pecoflyer]

It is somewhat annoying that ISBLANK returns FALSE whenever the referenced cell contains a formula, even though the formula may evaluate to give "".

It's perfectly normal. "" is a null string and thus is text ( try the TYPE() function to check that)
I made some modifications to the OP's file. Not quite sure about the logic in col J

 

[sxhenry20]

Thank you. That is exactly what I needed done, and I fixed the formula for J. It is working great now.

 

[Peter Bartholomew]

"" is a null string

Normal, yes. Helpful is another matter!
What is needed is a function that evaluates to give TRUE when tested with ISBLANK(). It would save 'spurious zero charting issues' too.

Since I have had a further look at the spreadsheet, I have attached a version. In each case perhaps we could have spelt the solutions out rather than simply suggesting ways forward.

Column J is simply a continuation of column E. Layouts like this are more of an issue for me because I normally avoid all direct cell referencing. The approach is designed to enforce uniformity (in order to reduce the possibility of error) but the downside is that capturing irregular data structures such as this become a challenge.