IF functions for cumulative column on downloaded bank statement

Hi all,

Would really appreciate your help!

I have downloaded a bank statement onto excel (numerical values appearing in 3 columns: paid in, paid out, balance) and wish to apply formulas to it - basically, a cumulative summary column alongside.


I have two problems/questions, although the first one is probably irrelevant as the second proposal might eradicate my format problem...(?) I look forward to receiving your replies! This is my first post to any computer forum - a last resort measure to try to address my IT inadequacies!

Thank you so much!!!




1. The basic formula I tried to use is not recognising the cells with no value. If I type in a "zero" then the formula works. If not, the "#VALUE!" error message appears.

What is the best way to correct this? I have tried changing the cell format. It is currency format. In my limited knowledge of excel I think I need to apply a formula: "IF there's a value in the cell, use it, IF NOT, apply a zero" so that I don't have to go down and enter a load of zeros!!!



2. In the whole of the cumulative column, not just cell by cell (for example column D) I would like the following formula but have no idea how to create it:
For example:
In D2: "If there's a value B2, subtract it, if there's a value in C2 add it, if there's no value in either then apply the last value on the last cell with a value in this column"

Thank you for your reply Fortis1991. Let me better explain both points:


1. How do I create this formula D2 = "D1 + (B2 if there's a value. If no value, use a zero in its place) - (C2 if there's a value. If no value, use a zero in its place) however if there's no value in either of these cells please 'imagine' that there is a zero in this cell with no value.

2. At my previous job we used a more complex formula. I want to try to recreate it.
It used the word "LAST" I think, and didn't matter if you deleted rows above the row in question as it wasn't dependent on the previous row/s, it was applied to the whole column and D189 for example took the value from any previous value in D, plus a value in C189, minus a value in B189.

This does not help me.

1. this is exactly the basic formula i already have and it is not working due to an error with the format because of downloaded bank values.
2. the formula DOES exist. As I said, we used it at my previous office.

Hopefully someone else will be able to advise?