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

185 pts.
Tags:
Excel 2007
Excel formulas
Microsoft Excel
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!
ASKED: April 29, 2009  6:49 PM
UPDATED: April 29, 2009  11:31 PM

Answer Wiki

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.

——————

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following