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
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
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,
Steve
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.
"junkmandan" wrote:
>
> 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
>
>
> --
> junkmandan
> ------------------------------------------------------------------------
> junkmandan's Profile: http://www.excelforum.com/member.php...o&userid=30866
> View this thread: http://www.excelforum.com/showthread...hreadid=505450
>
>
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.
--
Kevin Backmann
"junkmandan" wrote:
>
> 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
>
>
> --
> junkmandan
> ------------------------------------------------------------------------
> junkmandan's Profile: http://www.excelforum.com/member.php...o&userid=30866
> View this thread: http://www.excelforum.com/showthread...hreadid=505450
>
>
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).
junkmandan wrote:
>
> 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
>
> --
> junkmandan
> ------------------------------------------------------------------------
> junkmandan's Profile: http://www.excelforum.com/member.php...o&userid=30866
> View this thread: http://www.excelforum.com/showthread...hreadid=505450
--
Dave Peterson
"Kevin B" <[email protected]> wrote in message
news:[email protected]...
> 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.
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. :-)
You might just be right about that...
--
Kevin Backmann
"Doug Kanter" wrote:
> "Kevin B" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
> 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. :-)
>
>
>
You might just be right about that...
--
Kevin Backmann
"Doug Kanter" wrote:
> "Kevin B" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
> 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. :-)
>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks