Charting and ignoring 0s

[Shhhh]

Hello all,

I have a years worth of data that essentially looks like this:

Date P&L Fees
3/1/06 $4 $1
3/2/06 $466 $14
3/3/06 $15 $2
3/4/06 $0 $0
3/5/06 $0 $0

Basically the pattern is the same, with data and 0 values interspresed. I
would like to create a bar chart showing daily P&L for the entire year, but
the 0 values are really messing up the chart. Is there any way to tell the
chart to forget about the 0 values?

Please assume "Date" is in A1 and all adjacent cells are populated with
above data, i.e. "Fees" is in C1, and "3/5/06" is in A6.

Thank you so very much,
Shhhh

 

[Peo Sjoblom]

=IF(A1=0,NA(),A1)


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Shhhh]

Thanks, now the question is where do I input this formula?

Thanks,
Shhhh

 

[Peo Sjoblom]

You can insert a column next to the data and when you apply the graph you
use the formula range instead of the range with zeros or if the data is from
another formula use

=IF(formula=0,NA(),formula)

then replace the original formula with this formula


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Peo Sjoblom]

Or if the values are not from formulas just do an edit>replace and replace 0
with #N/A


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com