# Excel: simple sum help needed

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

Not open for further replies.

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

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

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

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

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

Joined:
Jul 2, 2005
Messages:
879

letchworth

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

Joined:
Mar 16, 2006
Messages:
30
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)

Joined:
Jan 19, 2005
Messages:
314
Brilliant thank u v much

10. ### 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.

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

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

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.

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

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.

File size:
22 KB
Views:
100
15. ### 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.

As Seen On