Excel Formula Help please...


  1. Posts : 491
    Windows 7
       #1

    Excel Formula Help please...


    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.
      My Computer


  2. Posts : 5,056
    Windows 7 x64 pro/ Windows 7 x86 Pro/ XP SP3 x86
       #2

    Try using IF ISBLANK.

    So, the formula could be something like

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


  3. Posts : 491
    Windows 7
    Thread Starter
       #3

    Bill2 said:
    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?
      My Computer


  4. Posts : 5,056
    Windows 7 x64 pro/ Windows 7 x86 Pro/ XP SP3 x86
       #4

    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.
      My Computer


  5. Posts : 491
    Windows 7
    Thread Starter
       #5

    Bill2 said:
    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.
      My Computer


  6. Posts : 3,009
    Windows 7 Home Premium 64bit
       #6

    Another possible is...


    You would of course need to drag the formula down through the rest of the column. :)
    Attached Thumbnails Attached Thumbnails Excel Formula Help please...-excel-formula.jpg  
      My Computer


  7. Posts : 3,009
    Windows 7 Home Premium 64bit
       #7

    Irene said:
    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.
      My Computer


 

  Related Discussions
Our Sites
Site Links
About Us
Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

© Designer Media Ltd
All times are GMT -5. The time now is 16:34.
Find Us