## Excel 2007 – How do I ignore zero %’s for a graph?

185 pts.
Tags:
Excel 2007
Microsoft Excel
Microsoft Excel formulas
Excel 2007 - How do I ignore zero %'s for a graph? example: you have data that looks something like this: week # 1 2 3 4 5 6 PTP% 10% 13% 0% 0% 0 % Then graph the PTP% across from week 1 which is 10% to week 6 which is 0% On the graph all u will see is the plots from week 1 & 2. But then you will see the charted line drop to zero from Week #2. My ? is does anyone know the formula i could use if the cells are zero%? Nesting more than 3 formulas sounds like the best way for this to work correctly. Any ideas would be great thanks. Jared~ PS: here's some example code i tried =IF(ISERROR(SUM(U166-U175-U171)),"",SUM(U166-U175-U171)) This code worked well when i had #DIV/0! in a cell. But now i need the 0% shown in cell to be not visible like the #DIV/0!

Thanks. We'll let you know when a new response is added.

It seems that the only way to achieve that is hiding the row/column with the 0%, so the graph will not include that value.

This can be done automatically. I found an example on the web:

Right click on the Sheet name tab and select “View Code”.
Then paste the following code and press ALT+Q to save.

<pre>Private Sub Worksheet_Calculate()
On Error Resume Next
Columns(5).SpecialCells _
(xlCellTypeFormulas, xlErrors).EntireRow.Hidden = True
Columns(5).SpecialCells _
(xlCellTypeFormulas, xlNumbers).EntireRow.Hidden = False
End Sub</pre>

This code will hide the complete row when the cell in column E (5) has an error, so you could add a formula in the PTP% column similar to this:

<pre>=IF(SUM(U166-U175-U171)=0,#N/A,SUM(U166-U175-U171))</pre>

When SUM(U166-U175-U171)=0 then this row will be hidden (and the graph will not include it), and when the value is different from 0 it will be un-hidden.

If your PPT% data is not in the E column you should modify the above code

I did a small test, and it worked.

Hope this helps.

——————