Windows Excel formula question please help

Discussion in 'Tech Support' started by Soniclord, 20 Jul 2011.

  1. Soniclord

    Soniclord What's a Dremel?

    Joined:
    14 Feb 2011
    Posts:
    256
    Likes Received:
    4
    I need help please with an excel formula which I think has gone wrong, it did work but I don't know what I did to 'break' it.

    The formula is =IF(A3<>"",D2-C3+B3,"") and it's what I use to add up amounts of money in a spreadsheet, the problem is in Column D3 I've got the amount £58.12 and then in column D4 I manually enter the amount of money in my partners bank account which BEFORE I enter that amount it has in the box the formula mentioned above =IF(A3<>"",D2-C3+B3,"") now previously when I entered any amount of money in Column D4 the formula added it up properly when I highlighted column D3's formula and selected all the other downward cells and clicked copy, now it doesn't!

    So basically I have the formula =IF(A3<>"",D2-C3+B3,"") in Column D3, the amount of money in my partners bank account in column D4 (NO FORMULA is showing) then in Column D5 the formula is back again as =IF(A5<>"",D4-C5+B5,"") so Column D4 (when I manually enter an amount of money) is skipping the formula so I get,

    Column D3 =IF(A3<>"",D2-C3+B3,"")
    Column D4 £189.46
    Column D5 =IF(A%<>"",D4-C5+B5,"")

    So obviously when I highlight column D3 then highlight all the other cells downwards and copy the formula it erases the amount of £189.46 and replaces it with the formula which should be there =IF(A3<>"",D3-C4+B4,"") but I want it to add up the amount of money in Column D4 as part of the formula so how do I get it to do that?? As I say I don't know how I 'broke' it in the 1st place.

    Oh and for the record the headings in Columns A1, B1, C1 and D1 are,

    Date| Money In| Paid Out| Balance

    And the spreadsheet only adds up/deducts the amounts in columns B and C, it ignores the amount of £58.12 in the 1st Column D2 and DOESN'T add it up with the rest of the money, so when I manually enter £189.46 and let's say further down in the Money In Column in Cell B7 it says £95.00 it adds that with the £189.46 totalling it as £284.46 which is correct but it ignores the £58.12 in Column D2.

    Sorry if this sounds overly complicated or makes no sense as I'm no excel wizard, also I won't be able to get back on here till later on today if someone can kindly help me fix it I'd appreciate it.

    Thank you.

    EDIT:

    What I need is for my spreadsheet to add Column D2+B3-C3

    D2 being Starting balance,
    B3 being Money in each week,
    C3 being Money Paid out each week

    So the remaining amount of money is in Column D4? Oh buggar it I don't know what I'm talking about when it comes to Excel, sorry I'm just overcomplicating things.

    Maybe this picture will help as it shows the formula and as can be seen it ignores Column D2 and doesn't add it to the total.

    [​IMG]
     
    Last edited: 20 Jul 2011
  2. deadsea

    deadsea What's a Dremel?

    Joined:
    9 Oct 2009
    Posts:
    197
    Likes Received:
    6
    You can't have a formula and a value occupy the same cell. Seperate out your partner's balance and then just expand your formula accordingly.
     
    sparkyboy22 likes this.
  3. Soniclord

    Soniclord What's a Dremel?

    Joined:
    14 Feb 2011
    Posts:
    256
    Likes Received:
    4
    Thanks for replying,

    How do I do that then?? Because I want it to include the £58.12 the 1st amount you see in the 1st column so when it totals the amounts that's also counted.

    How do I separate my partners balance then expand the formula to do it because as you can see it's adding the £189.46 and the £95.00 somewhere along the line to give a total of £284.46 but it's not including the £58.12 and anytime I change the amount of £189.46 should the balance go up or down then copy the formula it wipes that out as well?? I really am an Excel novice lol.
     
  4. sparkyboy22

    sparkyboy22 Web Tinkerer

    Joined:
    3 May 2010
    Posts:
    738
    Likes Received:
    35
    +rep for figuring out the question!
     
  5. deadsea

    deadsea What's a Dremel?

    Joined:
    9 Oct 2009
    Posts:
    197
    Likes Received:
    6
    The lazy method to reuse the formula would be to indicate your partner's balance as money in. Don't think he/she will appreciate that method of accounting though. =)

    If not, just insert another row. Right click on the column D, insert. Label that as Partner balance. Then edit the formula to the following.

    =IF(A3<>"",E2-C3+B3+D3,"")

    The Balance label should then be edited to Our balance for clarity too. If you want to maintain a seperate MY balance, That's another formula.
     
  6. sparkyboy22

    sparkyboy22 Web Tinkerer

    Joined:
    3 May 2010
    Posts:
    738
    Likes Received:
    35
    ^ Too Slow again ^

    Assuming that the money in and out plus the balance column are for your account you just need to add a column for partners account balance and another column for the total of column your account and partners account.
     
    Last edited: 20 Jul 2011
  7. Soniclord

    Soniclord What's a Dremel?

    Joined:
    14 Feb 2011
    Posts:
    256
    Likes Received:
    4
    Tried doing what you said and it still doesn't work, based on the screenshot of my spreadsheet below, where am I supposed to enter what you said?

    [​IMG]

    Because Column E2 is where txt is entered not any figures.
     
  8. Soniclord

    Soniclord What's a Dremel?

    Joined:
    14 Feb 2011
    Posts:
    256
    Likes Received:
    4
    Think I've worked it out, will post a pic when I've done it to see if it's right lol
     

Share This Page