• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Precise waffle charts|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Precise waffle charts|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Precise waffle charts
sp_PrintTopic sp_TopicIcon
Precise waffle charts
Avatar
Deirdre Leigh
Member
Members
Level 0
Forum Posts: 12
Member Since:
August 24, 2018
sp_UserOfflineSmall Offline
1
September 14, 2021 - 3:09 am
sp_Permalink sp_Print

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 attachedWaffle-chart.PNGImage Enlarger

.... 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

sp_PlupAttachments Attachments
  • sp_PlupImage Waffle-chart.PNG (18 KB)
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
September 14, 2021 - 9:49 am
sp_Permalink sp_Print

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

Avatar
Deirdre Leigh
Member
Members
Level 0
Forum Posts: 12
Member Since:
August 24, 2018
sp_UserOfflineSmall Offline
3
September 14, 2021 - 10:53 pm
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
September 15, 2021 - 2:55 pm
sp_Permalink sp_Print

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

Avatar
Deirdre Leigh
Member
Members
Level 0
Forum Posts: 12
Member Since:
August 24, 2018
sp_UserOfflineSmall Offline
5
September 15, 2021 - 8:18 pm
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
September 16, 2021 - 10:42 am
sp_Permalink sp_Print

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

Avatar
Deirdre Leigh
Member
Members
Level 0
Forum Posts: 12
Member Since:
August 24, 2018
sp_UserOfflineSmall Offline
7
September 16, 2021 - 8:51 pm
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Kim Knox, Mitchell Dahms, michael serna, Kathryn Patton
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
Forum Stats:
Groups: 3
Forums: 24
Topics: 6221
Posts: 27285

 

Member Stats:
Guest Posters: 49
Members: 31909
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.