Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting # of Formulas in a column with formulas and entered data | Excel Worksheet Functions | |||
Using Formulas for Data Validation | Excel Worksheet Functions | |||
How to sort data w/ formulas? ... | Excel Discussion (Misc queries) | |||
How do I calculate formulas for yes/no data | Excel Worksheet Functions | |||
Save Data not Formulas | Excel Discussion (Misc queries) |