Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Gin Tanaka   on Oct 22 In MS Office Category.

  
Question Answered By: Alarice Miller   on Oct 22

Two solutions to this maybe:
1. The uglier solution, but quicker: where you've got
=IF(OR(A2=0,B2=0),"",D2*1.5)
change it to
=IF(OR(A2=0,B2=0),NA(),D2*1.5)
The chart  won't plot  the #N/A cells  but will draw a line  directly from
one valid point to the next, ie. no gaps. The ugly side of this is the
presence of a lot of cells displaying  #N/A in the worksheet.

2. Leave your formula  as it was, but create a named range with a
formula: Go into Insert|Name|Define... and enter  say 'graph' (without
the single quote marks) in the 'Names in workbook' field. Then in the
'Refers to:' field paste something similar to the following:
=(IF(Sheet1!$E$2:$E$31<>"",Sheet1!$E$2:$E$31,NA()))
Then click the 'Add' button and close the dialogue box.
This will create a series  of values  like the cells in solution (1)
above, in the defined name 'graph' , and this is what we'll plot. Go
back to the chart and click on one of the points of your series so
that the whole series is selected. Look in the formula bar and you'll
see the likes of:
=SERIES(,,Sheet1!$E$2:$E$31,1)
It's the
'Sheet1!$E$2:$E$31'
that needs changing to
'Sheet1!graph,1)'
(without quote marks) and press Enter. (It may change to
=SERIES(,,Yourfilename!graph,1)
that's fine.)
Now you should see the same chart as in (1) above  but without all the
#N/As in the worksheet.

A third solution is to create a dynamic named range instead of the
static one above when a chart as proposed in my previous answer would
be produced.

Share: 

 

This Question has 4 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on Want chart not to plot zero values? Or get search suggestion and latest updates.


Tagged: