Hi,

I am trying to set up a spreadsheet to show my bank account balance

I want the 3 number columns to be

credit (D column) debit (E column) balance (F column)


now I have it working but...


if I
don't have a value in a debit column it displays #value in the balance
column.


So

I amend the cell formula as follows


=IF(E5="","",F4-E5+D5)

(IF debit column is empty THEN display blank ELSE display calculation
result)

but this only checks one column, the debit column

if I have a value in the credit column but leave the debit column empty
I get the #value error message.

I have put a zero in the debit column as a work-around but I would like
to fix it more elegantly

I tried using the OR function but that didn't work as one of the columns
always has a value

any help would be appreciated.



--
Nospam

"Nospam" wrote:
=IF(E5="","",F4-E5+D5)
[....]
but this only checks one column, the debit column

if I have a value in the credit column but leave
the debit column empty I get the #value error message.

One approach:

=SUM(F4,D5)-N(E5)


----- original message -----

"Nospam" wrote in message
...
Hi,

I am trying to set up a spreadsheet to show my bank account balance

I want the 3 number columns to be

credit (D column) debit (E column) balance (F column)


now I have it working but...


if I
don't have a value in a debit column it displays #value in the balance
column.


So

I amend the cell formula as follows


=IF(E5="","",F4-E5+D5)

(IF debit column is empty THEN display blank ELSE display calculation
result)

but this only checks one column, the debit column

if I have a value in the credit column but leave the debit column empty I
get the #value error message.

I have put a zero in the debit column as a work-around but I would like to
fix it more elegantly

I tried using the OR function but that didn't work as one of the columns
always has a value

any help would be appreciated.



--
Nospam

Sounds like maybe what you are calling the "blank cells" -- they might have a
"space character" in them, meaning " " ( a 1 length cell).. The space is
considered Text and the #VALUE! result usually is due to a text string
included in the Range being summed are added...

"Nospam" wrote:

Hi,

I am trying to set up a spreadsheet to show my bank account balance

I want the 3 number columns to be

credit (D column) debit (E column) balance (F column)


now I have it working but...


if I
don't have a value in a debit column it displays #value in the balance
column.


So

I amend the cell formula as follows


=IF(E5="","",F4-E5+D5)

(IF debit column is empty THEN display blank ELSE display calculation
result)

but this only checks one column, the debit column

if I have a value in the credit column but leave the debit column empty
I get the #value error message.

I have put a zero in the debit column as a work-around but I would like
to fix it more elegantly

I tried using the OR function but that didn't work as one of the columns
always has a value

any help would be appreciated.



--
Nospam

I think you are on to something JMay, I could not get it to duplicate the
error. I did however discover a quirk about using the empty string
comparison as opposed to zero. If I check for "" in a cell as part of an IF
formula, I get no result and nor error message. If I check for zero, I get
a correct answer

If(F4 "", F4 -(D5 - E5), "") leaves a blank F5, even if F4 has a number.

If(F4 0,F4 -(D5 - E5), "") leaves correct answer

I never knew that before. I thought Excel always treated "" as zero. Guess
not.


"JMay" wrote in message
...
Sounds like maybe what you are calling the "blank cells" -- they might
have a
"space character" in them, meaning " " ( a 1 length cell).. The space is
considered Text and the #VALUE! result usually is due to a text string
included in the Range being summed are added...

"Nospam" wrote:

Hi,

I am trying to set up a spreadsheet to show my bank account balance

I want the 3 number columns to be

credit (D column) debit (E column) balance (F column)


now I have it working but...


if I
don't have a value in a debit column it displays #value in the balance
column.


So

I amend the cell formula as follows


=IF(E5="","",F4-E5+D5)

(IF debit column is empty THEN display blank ELSE display calculation
result)

but this only checks one column, the debit column

if I have a value in the credit column but leave the debit column empty
I get the #value error message.

I have put a zero in the debit column as a work-around but I would like
to fix it more elegantly

I tried using the OR function but that didn't work as one of the columns
always has a value

any help would be appreciated.



--
Nospam