Ignoring Zero Values in Excel Graph

[Anuru]

Dear Experts,

I have here attached an excel file for your kind attention and help.

In this report there is an excel graph whose name is "Quality Index Chart May 2016". The data range for this is in "May 2016 Data sheet". My requirement is; "the CURVES should not drop down to "zero" values although the data sheet contains zero values in the range.

Thanks in advance.

Anuruddha

[MrShorty]

If you want a line or scatter chart to "ignore" a data point, it is usually recommended to use an N/A error value rather than 0. I suggest this essay http://peltiertech.com/mind-the-gap-...g-empty-cells/ In some cases, that may mean having two copies of the table -- one for you to look at with the 0 values, and one for the chart to look at with the N/A errors.

[Crooza]

In C4 put this formula to convert your 0's to NA#

=IF(B4/J4=0,NA(),B4/J4*100)

and drag down the column. Your chart will ignore these points

[Anuru]

Dear Crooza,

Thanks for your kind response.

I did as you have advised, but it didn't work...pls look at it again...

[Crooza]

It would be best if you uploaded a spreadsheet that could be edited.

[Anuru]

Dear Crooza,

This is an editable sheet. However, I noticed one problem in the formula. Formula works only if a figure is typed in D4, F4 and H4. If all those cells contain zeros formula shows "#DIV/0!". Secondly, even one of D4, F4 and H4 contains a value, formula works..

[JohnTopley]

In C4

=IFERROR(B4/J4*100,NA())

copy down

[Anuru]

Dear John,

Thanks a lot and your are really appreciated.

[Crooza]

Wasn't editable for me but as John says wrap an iferror around it although I'd wrap it around the formula I gave you otherwise you're going to be still stuck with zero figures and you'll be back to square one.

=Iferror(if(b4/j4=0,na(),b4/j4*100,na())