1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel: simple sum help needed

Discussion in 'Business Applications' started by cdpaul, Nov 8, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. cdpaul

    cdpaul Thread Starter

    Joined:
    Jan 19, 2005
    Messages:
    314
    I have done a simple income/expenditure sheet where f2+g2=h2, then i made the next line add the previous balance so i get a continual balance(h2) increase in the last column.

    I dragged the second formula down the page to #200 but now i have a complete column of 0.00 or whatever is the last result.

    Can i make it so the cells are blank until i add a new entry?

    Hope that makes sense, any help very much appreciated

    Thanx
     
  2. scotwithadev

    scotwithadev

    Joined:
    Mar 16, 2006
    Messages:
    30
    Try selecting column, then Tools|Options..View
    and remove tick from 'zero values'
     
  3. cdpaul

    cdpaul Thread Starter

    Joined:
    Jan 19, 2005
    Messages:
    314
    Thanx for such a quick reply.

    Unfortunately as soon as i filled in the first set of values (40.00) all the cells in the coulmn changed to 40.00 which is what im hoping to stop.

    Thanx
     
  4. scotwithadev

    scotwithadev

    Joined:
    Mar 16, 2006
    Messages:
    30
    In addition to switching off zero values , you could try this in H3
    =IF(AND(F3=0,G3=0),0,F3+G3+H2)
    and then drag formula in H3
     
  5. letchworth

    letchworth

    Joined:
    Jul 2, 2005
    Messages:
    879
    =IF(D9<>"";SUM(A9+C9+D9);"")

    D9 (in this case) is the cell you are referencing
    If it is blank, then the cell with this formula is also blank (no sub-total)== In my caseE9
    If there is something in D9, then the running total adds the previous sub totals in A9+C9 to what is the referenced cell

    (Of course my example needs to be adapted to your sub totals, etc.)
    Good luck,
    letchworth

    Edited: to fit your column example.
     
  6. letchworth

    letchworth

    Joined:
    Jul 2, 2005
    Messages:
    879
    Sorry, I got interrupted responding, I didn't notice that scotwithadev had already responded

    letchworth
     
  7. cdpaul

    cdpaul Thread Starter

    Joined:
    Jan 19, 2005
    Messages:
    314
    I got that to work thank u scotwithadev, i cant seem to get just that column to NOT show zero's tho and i would prefer to show a 0.00 in the credit column when im imputing in the debit column and vice versa.

    Is that possible? if not im happy to keep it how u've shown me.

    Thanx again, and thank u letchworth for yr time too
     
  8. scotwithadev

    scotwithadev

    Joined:
    Mar 16, 2006
    Messages:
    30
    okay, how about this.
    Leave zero values on in Tools|options so other columns show zero
    and swap
    =IF(AND(F3=0,G3=0),0,F3+G3+H2)
    for
    =IF(AND(F3=0,G3=0)," ",F3+G3+H2)
     
  9. cdpaul

    cdpaul Thread Starter

    Joined:
    Jan 19, 2005
    Messages:
    314
    Brilliant thank u v much
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    A couple of things to note about the subtle change which scotwithadev has proposed:

    1) It is replacing a numerical (0) return with a textual space. This space is different than a null value and will return a 1 from the formula =LEN(A1), where A1 houses this formula. This is significant because it can be deceiving (you can't "see" spaces) and interpreted wrong in formulas.

    2) Even a null value (="") will return a result when you perform a count (absolute) on it. These cells will also be returned as text, so any numerical manipulation on them will return the #VALUE! error.

    3) It's better to change the format of a cell (since you are asking to change the appearence, not to display zero values) rather than change it's actual return. Sure you could do it from Tools | Options, but it's much easier to select all the cells you wish this for and go to Format | Cells | Number and either choose a format or use a Custom format.


    A little about Custom formatting:

    There are four parts to custom formatting. Think of it something like this ...

    Part One; Part Two; Part Three; Part Four

    What does it all mean?
    Part One: Numerics greater than zero.
    Part Two: Numerics less than zero.
    Part Three: Numerics equal to zero.
    Part Four: Textual values.

    The default format is General. You can set this anyway you'd like. Although in custom formatting there are some special characters you can use. The first of these is the 0 (zero) symbol.

    0
    This stands for a single numeric placeholder. If Excel see's this it will hold a place for a numeric even if there is no number (hence keeping a zero in it's stead). As an example, the custom format "000" (without quotes) on a value of 23, you would see 023.

    #
    This also stands for a single numeric placeholder with one exception: Excel will not show this if no value is present/needed. So in the example above of the value 23 in a cell, using the custom format "###" (without quotes), you would see 23, not 023.

    There are some other custom formatting techniques that I will not get into here as I could be going for quite a while. The bottom line is, this is a job for formatting, not changing the equations (return).

    HTH
     
  11. scotwithadev

    scotwithadev

    Joined:
    Mar 16, 2006
    Messages:
    30
    So you mean use the formula
    =IF(AND(F3=0,G3=0),0,F3+G3+H2)
    but format column as
    _-* #,##0_-;-* #,##0_-;_-* #_-

    Thanks firefytr. I will bear that in mind in future.
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    You can use any custom format you want, the important one to manipulate is the third section, as it's the zero value. So you could use any of the styles you want, but leave the third part blank.

    A few examples might look like this ...

    Code:
    General;General;;General
    
    #,##0.00;[Red]-#,##0.00;;General
    
    0;0;;General
    Those are only a few examples. You can use 8 different colors conditionally like this as well (limited conditions).

    cdpaul, remember, this is a format, not a value. The difference is that a format is only manipulating how a cell(s) is displayed, whilst the underlying value is not changed. So if it evaluates to zero, then the zero value is still there, just not displayed.
     
  13. cdpaul

    cdpaul Thread Starter

    Joined:
    Jan 19, 2005
    Messages:
    314
    I have a "debit" and a "credit" column, if i dont put anything in either can i make it automatically show "0.00" please?

    Thank u
     
  14. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Here's a file containing some VBA (AKA "macros").

    Enter a date in A2. Type some text in B2 (e.g. ISP), press Enter and you'll be prompted for an amount. Type an amount (e.g. -20), press Enter.

    You should get 0.00 in C2, 20.00 in D2, and a running total (thanks to a formula) in E2.
     

    Attached Files:

  15. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    If you go to Tools | Options | Edit (tab), check Extend data range formats and formulas. Read up on the help files about it. If you have more than I believe 5 rows of the same formats and formulas, when you start typing in (credits/debits in your case) the formula you have will automatically populate itself. No need for VBA.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/516459

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice