Charting formula results showing 0 when it should be blank

[QualityData]

Thank you in advance for your expertise!!

I have a worksheet with dynamic charts using the indirect function (which I learned from chandoo.org). The raw data is a score value of 0-5 for each day of the month. But there are some days where there is no score, but because there is the Indirect formula in the cell, excel is charting these days as a 0 when it really should be blank. How do I get the chart to ignore the cells with no score?

 

[Luke M]

Can you change the INDIRECT formula to something like:

=IF(YourFormula = 0,NA(),Yourformula)


The #N/A error should be ignored by your chart.

 

[QualityData]

I'm sorry - I forgot to state that in addition to the score value of 0-5, there can also be an N/A. But I can't use your suggested formula because 0 is actually a score.


But maybe I could some other text that would be ignored. I'll try something like:

=IF(myformula = "","blank",myformula)


Or might you have another suggestion?


Thank you - and again I apologize for leaving out a piece of my puzzle.

 

[Luke M]

Is that a N/A as in "not applicable", or the #N/A error?

 

[QualityData]

That is N/A as in "not applicable".

 

[QualityData]

Okay - I re-read your solution and realized that the formula would show as the #N/A error message and that DID WORK!!!!!!


THANK YOU SO MUCH - you're my HERO!!!!!