Hi, i followed Mynda's video a few months ago and my first set of waffle charts were great, and brought some lovely compliments, but i seem to have hit a problem and the data is not always now updating correctly. One of my charts is based on the covid-19 vaccinations showing uptake of dose 1 and dose 2 so simple enough, so not sure where i have gone wrong.
Here's a snip of my data and the waffle chart attached
.... and the formulae in the dose 2 and dose 1 cells for the error bars 1 row
=MAX(MIN(Z$5*100-($E10-1)*10,10),0)
=(10-F10)+(MAX(MIN(F$6*100-($E10-1)*10,10),0))-10
Any help would be appreciated, thanks
Deirdre
Hi Deidre,
Welcome to our forum!
I can't tell from the screenshot what the cell references are and then compare it to my example. I can see that the formulas are structured correctly, so there must be an issue with the cells being referenced. I recommend you compare your file to my example file to troubleshoot where the reference is incorrect.
Keep in mind that my series data shows the incremental change from one series to the next and in column D I have the cumulative position. In your data you only have the series absolute values. This may be where you're going wrong.
Hope that helps point you in the right direction. If you're still stuck, please upload a sample excel file so we can help you further.
Mynda
Thanks Mynda, I've checked my formulae but can't see where I'm going wrong, so have attached a copy of my excel file. There are 5 waffle charts all showing progress on dose 1 and dose 2 of the covid vaccination in different areas. I've numbered them to make reference easier.
Dose 1 appears ok on all 5 charts and charts 1,3 and 4 appear ok for dose 2. But charts 2 (red) and 5 (purple) aren't right for dose 2. I can see that I'm only getting data in error bar 9 for both, whereas the correct charts have data in error bars 9 and 10 in charts 1 and 3 (and 8 and 9 in chart 4), if that's causing the issue, but still can't see where the problem is, and why in only 2 of the charts when the formulae is the same in all 5.
Deirdre
Hi Deidre,
These charts look correct to me. Chart 2 has 88.9% for Dose 1, which takes you to the 9th row of the chart i.e. nearly 90%. Chart 1 is 90.8% for Dose 1 which is into the 10th row i.e. over 90%, also correct. Chart 5 has 89.5% for Dose 1 which is into the 9th row i.e. almost 90%.
I'm not sure why you think charts 2 and 5 are wrong.
Mynda
Hi Mynda, yes I agree that they look correct. I was being fooled by the fact that if you hover over the last square (as i tend to do to check it) in chart 1 you get the value shown as 0.814 etc which matches the value in cell C17, and can be seen to equal the value of 90.8% in B5. But for chart 2, hovering brings up the value of 8.240 etc as in G16, but it's not visibly the same as 88.9% in F5. Hope that makes sense. Same with chart 5.
Thanks for the tip in the same video about Ctrl and the up arrow to select the major gridlines - that was annoying me.
Deirdre
Hi Deirdre,
I disagree. To me the position of the final pink square in chart 2 represents 88.9% i.e. there are 88 fully shaded squares and the final square is 90% or .9 filled, equating to a total of 88.9%.
Likewise, in chart 5 there are 89 fully shaded squares plus half a shaded square equating to 89.5%.
Hovering over the squares in the chart is simply going to show you the value for that square, which will never marry up to the cumulative position of that particular square. Unfortunately, you cannot turn off the tooltip that appears on hover.
Mynda
Thanks Mynda, i agree that the squares are fine, only the hover tooltip that's different (in that it appears correct in 3 charts but not in the 2 I was querying). But I am now happy that my charts are correct (they are provided to senior figures) so thanks again (and for all your videos, I have watched quite a few and used a number of ideas; now need to further my dashboard knowledge so will be back).
Deirdre
