Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default Graphing 0 values from blank cells

My formula is:
=INDIRECT("'Enter data'!i$19")

i19 in the "Enter data" sheet referenced in the formula is blank, but the
formula returns a "0" in the cell. I do not want a "0". I just want it
blank. And it is graphing a "0" value when I create a graph based on the
worksheet with the formulas.

Any suggestions on how I can prevent/change this from happening so that the
"0" is not returned or graphed.
Thank you.
  #2   Report Post  
Posted to microsoft.public.excel.misc
CM CM is offline
external usenet poster
 
Posts: 136
Default Graphing 0 values from blank cells

change formula to:

=if(INDIRECT("'Enter data'!i$19")=0,"",INDIRECT("'Enter data'!i$19"))



"Mike" wrote:

My formula is:
=INDIRECT("'Enter data'!i$19")

i19 in the "Enter data" sheet referenced in the formula is blank, but the
formula returns a "0" in the cell. I do not want a "0". I just want it
blank. And it is graphing a "0" value when I create a graph based on the
worksheet with the formulas.

Any suggestions on how I can prevent/change this from happening so that the
"0" is not returned or graphed.
Thank you.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,276
Default Graphing 0 values from blank cells

Hi,
if(INDIRECT("'Enter data'!i$19")="","",INDIRECT("'Enter data'!i$19"))

"Mike" wrote:

My formula is:
=INDIRECT("'Enter data'!i$19")

i19 in the "Enter data" sheet referenced in the formula is blank, but the
formula returns a "0" in the cell. I do not want a "0". I just want it
blank. And it is graphing a "0" value when I create a graph based on the
worksheet with the formulas.

Any suggestions on how I can prevent/change this from happening so that the
"0" is not returned or graphed.
Thank you.

  #4   Report Post  
Posted to microsoft.public.excel.misc
KC KC is offline
external usenet poster
 
Posts: 94
Default Graphing 0 values from blank cells

Mike,
can you try

=IF(INDIRECT("'Enter data'!i$19")=0,"",INDIRECT("'Enter data'!i$19"))

-kc
-click YES if this works!

"Mike" wrote:

My formula is:
=INDIRECT("'Enter data'!i$19")

i19 in the "Enter data" sheet referenced in the formula is blank, but the
formula returns a "0" in the cell. I do not want a "0". I just want it
blank. And it is graphing a "0" value when I create a graph based on the
worksheet with the formulas.

Any suggestions on how I can prevent/change this from happening so that the
"0" is not returned or graphed.
Thank you.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default Graphing 0 values from blank cells

1/2 way there. Thank you.

This formula removed the "0" from the spreadsheet, but the "0" is still
graphed as a 0 value in the graph.

I even went back and re-selected the data, saved the file, closed and
reopened.

What next?

Thank you.

"cm" wrote:

change formula to:

=if(INDIRECT("'Enter data'!i$19")=0,"",INDIRECT("'Enter data'!i$19"))



"Mike" wrote:

My formula is:
=INDIRECT("'Enter data'!i$19")

i19 in the "Enter data" sheet referenced in the formula is blank, but the
formula returns a "0" in the cell. I do not want a "0". I just want it
blank. And it is graphing a "0" value when I create a graph based on the
worksheet with the formulas.

Any suggestions on how I can prevent/change this from happening so that the
"0" is not returned or graphed.
Thank you.



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default Graphing 0 values from blank cells

This formula with the quadruple quotes after the equal sign did not remove
the 0's from the graph. It removed the 0's from the spreadsheet, but it is
still graphing 0's on the graph.

The data selected for the graph does contain these cells that I've changed,
but still graphing 0's.

Any other ideas?

Thanks.

"Eduardo" wrote:

Hi,
if(INDIRECT("'Enter data'!i$19")="","",INDIRECT("'Enter data'!i$19"))

"Mike" wrote:

My formula is:
=INDIRECT("'Enter data'!i$19")

i19 in the "Enter data" sheet referenced in the formula is blank, but the
formula returns a "0" in the cell. I do not want a "0". I just want it
blank. And it is graphing a "0" value when I create a graph based on the
worksheet with the formulas.

Any suggestions on how I can prevent/change this from happening so that the
"0" is not returned or graphed.
Thank you.

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Graphing 0 values from blank cells

=if(INDIRECT("'Enter data'!i19")="",na(),INDIRECT("'Enter data'!i19"))

If you don't like the N/A's, you could hide them using conditional formatting.





Mike wrote:

My formula is:
=INDIRECT("'Enter data'!i$19")

i19 in the "Enter data" sheet referenced in the formula is blank, but the
formula returns a "0" in the cell. I do not want a "0". I just want it
blank. And it is graphing a "0" value when I create a graph based on the
worksheet with the formulas.

Any suggestions on how I can prevent/change this from happening so that the
"0" is not returned or graphed.
Thank you.


--

Dave Peterson
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default Graphing 0 values from blank cells

Dave,
Thanks. 0's are removed from graph.
Thanks to all for quick help.
Mike


"Dave Peterson" wrote:

=if(INDIRECT("'Enter data'!i19")="",na(),INDIRECT("'Enter data'!i19"))

If you don't like the N/A's, you could hide them using conditional formatting.





Mike wrote:

My formula is:
=INDIRECT("'Enter data'!i$19")

i19 in the "Enter data" sheet referenced in the formula is blank, but the
formula returns a "0" in the cell. I do not want a "0". I just want it
blank. And it is graphing a "0" value when I create a graph based on the
worksheet with the formulas.

Any suggestions on how I can prevent/change this from happening so that the
"0" is not returned or graphed.
Thank you.


--

Dave Peterson

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
TRANSPOSE results in zero values for blank cells ramatsu Excel Worksheet Functions 4 July 14th 09 12:03 AM
Graphing blank cells as gaps Todd Excel Discussion (Misc queries) 1 July 1st 09 08:40 PM
If certain cells not blank, and cells in range are, set values to ktoth04 Excel Discussion (Misc queries) 0 February 21st 08 10:01 PM
blank cells plot as zero values Pete Nelson Charts and Charting in Excel 2 July 18th 06 02:59 AM
How to blank out cells with duplicate values? Bleu_808 Excel Discussion (Misc queries) 8 May 2nd 06 10:16 PM


All times are GMT +1. The time now is 12:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"