Formulas not data 0
 

Hi guys

I want to somehow create a formula, which give me the following results...

I want it to SUM a serious of cells

e.g. SUM(A1:A5)

However, if there are no results in cells A1 to A5, I don't want the SUM
cell to display a 0

I want for it to stay blank.

Please advise

thanks

Formulas not data 0
 

Hi Ravi,

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

or else you can turn off displaying zeroes in ToolsOptions Edit tab,
although this will apply to the whole workbook.

--

HTH

Bob Phillips

"Ravi Sandhu" wrote in message
...
Hi guys

I want to somehow create a formula, which give me the following results...

I want it to SUM a serious of cells

e.g. SUM(A1:A5)

However, if there are no results in cells A1 to A5, I don't want the SUM
cell to display a 0

I want for it to stay blank.

Please advise

thanks

Formulas not data 0
 

Hi Bob!

What you meant was:

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


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Saturday 2nd August: Andorra (Andorra La
Vella), Bosnia – Herzegovina (Ilindan), Costa Rica (Virgin of the
Angels Day), Macedonia (Ilinden / St. Elijah’s Day), St. Kitts & Nevis
(Emancipation Day), Yugoslavia (Ilinden / St. Elijah’s Day).

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Bob Phillips" wrote in message
...

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

Formulas not data 0
 

On Sat, 2 Aug 2003 08:57:58 +0100, "Ravi Sandhu" wrote:

Hi guys

I want to somehow create a formula, which give me the following results...

I want it to SUM a serious of cells

e.g. SUM(A1:A5)

However, if there are no results in cells A1 to A5, I don't want the SUM
cell to display a 0

I want for it to stay blank.

Please advise

thanks


In addition to what others have posted, you could keep your formula the same
and use a custom format for that cell:

Format/Cells/Number/Custom/Type: #,##0.00;-#,##0.00;;

That's an example of formatting with commas and two decimal places. In the
format string, semicolons (;) are used as separators, and the third entry is
for 0 values. Since there is nothing there, a zero is displayed as a blank,
although the value is still in the cell.


--ron

Formulas not data 0
 

Confidence over caution! Thought I couldn't get a simple formula like that
wrong, that will teach me!

Thanks for ensuring the OP gets a correct answer.

--

HTH

Bob Phillips

"Norman Harker" wrote in message
...
Hi Bob!

What you meant was:

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


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Saturday 2nd August: Andorra (Andorra La
Vella), Bosnia - Herzegovina (Ilindan), Costa Rica (Virgin of the
Angels Day), Macedonia (Ilinden / St. Elijah's Day), St. Kitts & Nevis
(Emancipation Day), Yugoslavia (Ilinden / St. Elijah's Day).

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Bob Phillips" wrote in message
...

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