Q:
Excel 2007 - How do I ignore zero %'s for a graph?
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!
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!
ASKED:
Apr 29 2009 6:49 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
+1
Click to Vote:
+1
Click to Vote:
- 1
0
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.
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
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:
=IF(SUM(U166-U175-U171)=0,#N/A,SUM(U166-U175-U171))
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.
------------------
