Tech Support Guy banner
  • Please post in our Community Feedback thread for help with the new forum software! If you are having trouble logging in, please Contact Us for assistance.
Status
Not open for further replies.
1 - 4 of 4 Posts

·
Registered
Joined
·
99 Posts
Discussion Starter · #1 ·
I have a chart that shows performance each week for a year. Its source is from a spreadsheet that has formulas in it referring to each week. The problem is that the chart drops and reflects zero values for the weeks that don't have data yet. (the remainder of the year). The Tools, Options, Chart, Plot Empty Cells option does not work since the cells are not empty, but instead have a formula in them. Any suggestions on how to plot only the cells with data in them? Thanks.
 

·
Registered
Joined
·
2,702 Posts
One way, amend the formulas so they return #N/A instead of zero (#N/A won't plot in a chart).

Example: with random values including zeros in A1:A10, use

=IF(A1=0,#N/A,A1*3)

in B1 & copy down to B10. Then chart B1:B10.

Rgds,
Andy
 

·
Registered
Joined
·
99 Posts
Discussion Starter · #3 ·
Thanks for the help. Here is the outcome I ended up using:

=if(formula="",na(),formula)

which is similar to what you had. I didn't know about NA before this time, but stumbled across it in other research. Thanks so much for the help.

Is there a way that it will not display #NA in the cell but leave it blank?
 

·
Registered
Joined
·
2,702 Posts
There's a difference between a cell being blank & displaying blank.

For displaying, one method* is to use a conditional formatting formula** like:

=ISNA(B1)

, then Format so that where TRUE, font colour = cell colour.

* Excel 97 or later

** see Format menu -- Conditional Formatting sub-menu.
 
1 - 4 of 4 Posts
Status
Not open for further replies.
Top