How to insert a formula..but...

J

junkmandan

How can I insert a formula, say the sum of a column, without the cell
showing a zero sum, until the formula has been applied. My form looks
messy with all the zero values without any other information in the
form.

Thanks
 
J

Jonathan Cooper

Change your cell format. The format of a cell is broken down into four
sections, seperated by a semi-colon. The first tells excel what to do with
positive numbers. Then negative numbers, THEN ZERO'S, and lastly text. You
need to change that third section as follows.

For example, $#,##0.00_);[Red]($#,##0.00);""

The double quotes will make them appear blank.
 
S

SteveG

Assuming your range to sum is A1:A10 then

=IF(SUM(A1:A10)=0,"",SUM(A1:A10))

This will leave the cell blank until data is entered in the range.

Cheers,

Stev
 
K

Kevin B

Click on Tools in the menu and click on Options. In the Options dialog box
click on the View tab and locate the Check Box that says Zero Values. The
check box is located in the Windows Options panel of the View page. Click
this off to have zero values suppressed.
 
D

Dave Peterson

Maybe ...

=if(counta(a1:a10)<10,"",sum(a1:a10))

It waits until there's something in all 10 cells before doing the sum. Make
sure that number matches the number of cells in the range (I used 10 and
A1:A10).
 
D

Doug Kanter

Kevin B said:
Click on Tools in the menu and click on Options. In the Options dialog
box
click on the View tab and locate the Check Box that says Zero Values. The
check box is located in the Windows Options panel of the View page. Click
this off to have zero values suppressed.
Click to expand...

That might drive him nuts 2 years from now when he *wants* zeros to show on
a certain sheet, and doesn't remember having adjusted that option. :)
 
Top