Graph is considering blank as zero

[Zubair]

I am using following formula and on blank cells graph is showing zero, is there any change in my formula which can not shown plot if cell is blank?


=IF(A30="","",INDEX($Y$7:$AB$33,$I30,3))

 

[mart37]

=IF(A30="",NA(),INDEX($Y$7:$AB$33,$I30,3))

 

[Zubair]

Thanks mart37, Graph get corrected but #N/A is appearing in the data how can I remove that in same formula ?

 

[mart37]

Did you tried:

1. Click the chart you want to change.
2. Go to Chart Tools on the Ribbon, then on the Design tab, in the Data group, click Select Data.
   
   
3. Click Hidden and Empty Cells.
   
   
4. In the Show empty cells as: options box, click Gaps, Zero, or Connect data points with line.

 

[Peter_SSs]

Perhaps you mean to hide the #N/A result in the formula cell?
If so, you could use Conditional Formatting to do that as shown in cell A5 below.

21 04 06.xlsm
	A
1
2
3
4
5	#N/A
Sheet2

Cell Formulas
Range	Formula
A5	A5	=IF(A30="",NA(),INDEX($Y$7:$AB$33,$I30,3))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5	Expression	=ISNA(A5)	text	NO

 

[Zubair]

Peter - Not able to apply conditional Formatting, please share steps.

 

[Peter_SSs]

Peter - Not able to apply conditional Formatting, please share steps.

Select the cell
Home ribbon tab -> Conditional Formatting -> New rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true:-
=ISNA(A5) (instead of A5 put the address of the cell you have selected) -> Format... -> Font tab -> Choose white (or whatever colour matches your cell background colour) -> OK -> OK

 

[Zubair]

Yes working Thank Peter & mart37

 

[Peter_SSs]

You're welcome. Glad we could help.