Hiding zeros in a Chart

[amartino44]

Hello. I have charts that show monthly trends. I have to update this chart about twice a week. At the beginning of the month, there are obviously a lot of cells with no information in them. At first, I selected all of the days of the month but that showed them as zeros in the charts. I couldn't figure out the solution so I just reselected the data each month to correspond to the last day with data. Today, I tried puting in a If/Then formula with "#NA" for zeros, but that didn't work. Any ideas? I KNOW that this is possible, I just don't know how. Thanks.

[Richard Buttrey]

Have you tried

=IFERROR(yourformula,NA())

[amartino44]

Well, that won't work....My formula is a sum formula and it either equals to a number or it equals 0 (because their is no data), so the formula you proposed will just result in 0 This was the formula that I used =IF(SUM(NB119+NB120)=0,"#NA",SUM(NB119+NB120)) but it also didn't work. Thanks.

[jeffreybrown]

By zero on the chart, my impression is you may be referring to the data labels?

If so, did you look into a custom format >> 0;;;

[Andy Pope]

try modifying the formula as Richard suggested.

=IF(SUM(NB119+NB120)=0,NA(),SUM(NB119+NB120))

Alternatively look into using a dynamic named range to only plot data up to current period

[amartino44]

Sweet. That worked!! I was inserting "#N/A" rather than NA(). What is the difference? Thanks.

[Richard Buttrey]

Hi,

"#N/A" is just a string of characters and means nothing (apart from that) to Excel
NA() on the other hand is an Excel function, just as SUM() is a function, and means something very specific to Excel in terms of what it will do.