| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to have cell values of 0 showing as blank
I am linking data from another worksheet, but I want any value which is 0 to
show a blank cell. What would be the easies way to do this? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to have cell values of 0 showing as blank
=IF(Sheet3!A1<"",Sheet3!A1,"")
=IF(Sheet3!A1=0,"",Sheet3!A1)# try either of these "Alex" wrote: I am linking data from another worksheet, but I want any value which is 0 to show a blank cell. What would be the easies way to do this? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to have cell values of 0 showing as blank
Worked beautifully, is there any way of not getting those 0's to not show on
a chart as well? "Mike" wrote: =IF(Sheet3!A1<"",Sheet3!A1,"") =IF(Sheet3!A1=0,"",Sheet3!A1)# try either of these "Alex" wrote: I am linking data from another worksheet, but I want any value which is 0 to show a blank cell. What would be the easies way to do this? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to have cell values of 0 showing as blank
Sorry I don't do charts. Thanks for the feedback.
"Alex" wrote: Worked beautifully, is there any way of not getting those 0's to not show on a chart as well? "Mike" wrote: =IF(Sheet3!A1<"",Sheet3!A1,"") =IF(Sheet3!A1=0,"",Sheet3!A1)# try either of these "Alex" wrote: I am linking data from another worksheet, but I want any value which is 0 to show a blank cell. What would be the easies way to do this? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to have cell values of 0 showing as blank
Hi Alex
If you want the chart to ignore zero values you would need to use NA() in place of Null in Mike's formulae =IF(Sheet3!A1<"",Sheet3!A1,NA()) This will show #N/A in the cells, which the Chart will ignore as a datapoint. If the aesthetics of having #N/A showing on the sheet bothers you, then you could apply conditional formatting on the cells to set the Font the same colours as the background of the cell. In the CF, use Formula Is and =NOT(ISNUMBER(A1)) -- Regards Roger Govier "Alex" wrote in message ... Worked beautifully, is there any way of not getting those 0's to not show on a chart as well? "Mike" wrote: =IF(Sheet3!A1<"",Sheet3!A1,"") =IF(Sheet3!A1=0,"",Sheet3!A1)# try either of these "Alex" wrote: I am linking data from another worksheet, but I want any value which is 0 to show a blank cell. What would be the easies way to do this? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to have cell values of 0 showing as blank
I did that, but it gets stranger, the value pn the chart also shows as #N/A.
Why would this be? "Roger Govier" wrote: Hi Alex If you want the chart to ignore zero values you would need to use NA() in place of Null in Mike's formulae =IF(Sheet3!A1<"",Sheet3!A1,NA()) This will show #N/A in the cells, which the Chart will ignore as a datapoint. If the aesthetics of having #N/A showing on the sheet bothers you, then you could apply conditional formatting on the cells to set the Font the same colours as the background of the cell. In the CF, use Formula Is and =NOT(ISNUMBER(A1)) -- Regards Roger Govier "Alex" wrote in message ... Worked beautifully, is there any way of not getting those 0's to not show on a chart as well? "Mike" wrote: =IF(Sheet3!A1<"",Sheet3!A1,"") =IF(Sheet3!A1=0,"",Sheet3!A1)# try either of these "Alex" wrote: I am linking data from another worksheet, but I want any value which is 0 to show a blank cell. What would be the easies way to do this? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to have cell values of 0 showing as blank
I am having a problem similar to Alex's. I am trying to hide the #N/A
symbol. I tried the conditional formatting angle, but it didn't work. Any suggestions? "Roger Govier" wrote: Hi Alex If you want the chart to ignore zero values you would need to use NA() in place of Null in Mike's formulae =IF(Sheet3!A1<"",Sheet3!A1,NA()) This will show #N/A in the cells, which the Chart will ignore as a datapoint. If the aesthetics of having #N/A showing on the sheet bothers you, then you could apply conditional formatting on the cells to set the Font the same colours as the background of the cell. In the CF, use Formula Is and =NOT(ISNUMBER(A1)) -- Regards Roger Govier "Alex" wrote in message ... Worked beautifully, is there any way of not getting those 0's to not show on a chart as well? "Mike" wrote: =IF(Sheet3!A1<"",Sheet3!A1,"") =IF(Sheet3!A1=0,"",Sheet3!A1)# try either of these "Alex" wrote: I am linking data from another worksheet, but I want any value which is 0 to show a blank cell. What would be the easies way to do this? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to have cell values of 0 showing as blank
Hi Catherine
Mark the range of cells where your #N/A's appear. FormatConditional Formattinguse dropdown for Formula is =NOT(ISNUMBER(A1)) Replace A1 with the cell address of the first cell in your marked range. -- Regards Roger Govier "Catherine" wrote in message ... I am having a problem similar to Alex's. I am trying to hide the #N/A symbol. I tried the conditional formatting angle, but it didn't work. Any suggestions? "Roger Govier" wrote: Hi Alex If you want the chart to ignore zero values you would need to use NA() in place of Null in Mike's formulae =IF(Sheet3!A1<"",Sheet3!A1,NA()) This will show #N/A in the cells, which the Chart will ignore as a datapoint. If the aesthetics of having #N/A showing on the sheet bothers you, then you could apply conditional formatting on the cells to set the Font the same colours as the background of the cell. In the CF, use Formula Is and =NOT(ISNUMBER(A1)) -- Regards Roger Govier "Alex" wrote in message ... Worked beautifully, is there any way of not getting those 0's to not show on a chart as well? "Mike" wrote: =IF(Sheet3!A1<"",Sheet3!A1,"") =IF(Sheet3!A1=0,"",Sheet3!A1)# try either of these "Alex" wrote: I am linking data from another worksheet, but I want any value which is 0 to show a blank cell. What would be the easies way to do this? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to have cell values of 0 showing as blank
Thank you very much. That worked perfectly.
"Roger Govier" wrote: Hi Catherine Mark the range of cells where your #N/A's appear. FormatConditional Formattinguse dropdown for Formula is =NOT(ISNUMBER(A1)) Replace A1 with the cell address of the first cell in your marked range. -- Regards Roger Govier "Catherine" wrote in message ... I am having a problem similar to Alex's. I am trying to hide the #N/A symbol. I tried the conditional formatting angle, but it didn't work. Any suggestions? "Roger Govier" wrote: Hi Alex If you want the chart to ignore zero values you would need to use NA() in place of Null in Mike's formulae =IF(Sheet3!A1<"",Sheet3!A1,NA()) This will show #N/A in the cells, which the Chart will ignore as a datapoint. If the aesthetics of having #N/A showing on the sheet bothers you, then you could apply conditional formatting on the cells to set the Font the same colours as the background of the cell. In the CF, use Formula Is and =NOT(ISNUMBER(A1)) -- Regards Roger Govier "Alex" wrote in message ... Worked beautifully, is there any way of not getting those 0's to not show on a chart as well? "Mike" wrote: =IF(Sheet3!A1<"",Sheet3!A1,"") =IF(Sheet3!A1=0,"",Sheet3!A1)# try either of these "Alex" wrote: I am linking data from another worksheet, but I want any value which is 0 to show a blank cell. What would be the easies way to do this? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to have cell values of 0 showing as blank
assuming that the 0 is a result of a formula
=if((yourformula)=0,"",(yourformula)) "Catherine" wrote in message ... Thank you very much. That worked perfectly. "Roger Govier" wrote: Hi Catherine Mark the range of cells where your #N/A's appear. FormatConditional Formattinguse dropdown for Formula is =NOT(ISNUMBER(A1)) Replace A1 with the cell address of the first cell in your marked range. -- Regards Roger Govier "Catherine" wrote in message ... I am having a problem similar to Alex's. I am trying to hide the #N/A symbol. I tried the conditional formatting angle, but it didn't work. Any suggestions? "Roger Govier" wrote: Hi Alex If you want the chart to ignore zero values you would need to use NA() in place of Null in Mike's formulae =IF(Sheet3!A1<"",Sheet3!A1,NA()) This will show #N/A in the cells, which the Chart will ignore as a datapoint. If the aesthetics of having #N/A showing on the sheet bothers you, then you could apply conditional formatting on the cells to set the Font the same colours as the background of the cell. In the CF, use Formula Is and =NOT(ISNUMBER(A1)) -- Regards Roger Govier "Alex" wrote in message ... Worked beautifully, is there any way of not getting those 0's to not show on a chart as well? "Mike" wrote: =IF(Sheet3!A1<"",Sheet3!A1,"") =IF(Sheet3!A1=0,"",Sheet3!A1)# try either of these "Alex" wrote: I am linking data from another worksheet, but I want any value which is 0 to show a blank cell. What would be the easies way to do this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct issues | Excel Worksheet Functions | |||
| Summing (or counting) cells until there is a blank cell | Excel Discussion (Misc queries) | |||
| IF function won't post cell values | Excel Discussion (Misc queries) | |||
| Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions | |||
| GET.CELL | Excel Worksheet Functions | |||
Linear Mode
