Excel Formula Help please...

[BomberAF]

This is probably very simple and I have got it working but it isn't working how I want it to work.

I have created an Excel document to show my monthly payments of a bank loan. I have created 3 columns in the first column is the payment number, in the second column is the amount that the loan is after I have made a payment, and in the third column I want to know how much the loan has been reduced by after the bank has taken interest off.

I have just put this formula in the 3rd column, =SUM(D4-D5), and then dragged this formula down into all the other rows I want this to work in which it does. The problem I am having with this is that the number from the 2nd column get placed in the 3rd row, below the amount that has been reduced, and I would like it to stay at £0.00 as it looks better.

Here is what I mean

If you notice the number I enter in the balance column appears in the reduced by column, and I don't want it too because it looks very unprofessional.

Can someone please help me out with this please.

 

[Bill2]

Try using IF ISBLANK.

So, the formula could be something like

=IF(ISBLANK(D5),"",SUM(D4-D5))

 

[BomberAF]

Try using IF ISBLANK.

So, the formula could be something like

=IF(ISBLANK(D5),"",SUM(D4-D5))

Thanks for that it worked a treat, now I know I am being fussy, but all the cells that had £0.00 in the rest of the spreadsheet have just gone blank.

How can I get the cells to have £0.00 in them again, but keep the value from being carried?

 

[Bill2]

Try this.

=IF(ISBLANK(D5),0,SUM(D4-D5))

Otherwise, you could insert a string

=IF(ISBLANK(D5),"£0.00",SUM(D4-D5))

You may need to adjust the formatting etc.

 

[BomberAF]

Try this.

=IF(ISBLANK(D5),0,SUM(D4-D5))

Otherwise, you could insert a string

=IF(ISBLANK(D5),"£0.00",SUM(D4-D5))

You may need to adjust the formatting etc.

Once again many thanks that was very helpfull I have learned something today, just wish I could rep you more but alas I can only rep you once, if I see you around I will give you some more.

 

[Irene]

Another possible is...


You would of course need to drag the formula down through the rest of the column.

 

[Irene]

Another possible is...


You would of course need to drag the formula down through the rest of the column.

This formula basically says:
If the number in column 'C' is more than zero, take C3 from C2, otherwise enter zero in the column 'D' cell.