I have a compliance spreadsheet that feeds into a bar chart that sums the total number of observations and the number of compliant observations. I divide these to get a compliance %. On the spreadsheet, I have specified that I want to see the words "no data", rather than the error message, when the 'Observed' field equals zero. On my chart, that data point still shows as 0%. What I would like to see on the chart is "no data" if there are zero observations, and 0% only if there are observations but none are compliant.
The reason that I am asking is that I want to be able to distinguish between no data being submitted and a lack of compliance. Perhaps there is a better way to do that...so any and all suggestions are welcome!
I have attached a sample of my data.