• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Chart alternatives same data: FB evo compare

Stephan

Member
Hello, see attached file, all main chart types from same yes/no data table, presenting more valid alternatives then Radar chart for demonstrating trends/majoritites.

Further to FB evo RADAR chart debate, ie can you do that in Excel, yes but they are less sophisticed, and as many have pointed out Excel Radar charts are ineffective data visualisations.

Think alot of confusion is what does data table look like, well its a YES NO table with categories. Hence table with numerous Y characters, but Y isn't recognised by Excel, hence changed Y value > 1 value. In other words instead of letter character, this is replaced with numeric 1 value to demonstrate its a YES!

Data table: 1st tab Bar Vert, Range: A25:L30. Presumably data would have been demonstrated differently for decision factor, see last tab Sheet Y Grid H & V, Range: A1:BD3.
Presumably data was stacked to make differences more obvious, Sheet Y Grid H & V, Range: A5:L10.
H = Horizontal, V = Vertical.

File attached is several different chart types from YES/NO table data, personnely I think the most effective at demonstrating is in following order, considering compelling to look at that doesn't require too much thought, but looks more appealing then basic chart:
Surface 3D > Area 3D > Area > Bar Vert > Bar Horiz > Pyramid.

I'd say Excel 2003 Radar or Donuts charts for this dataset are useless, apart from demonstrating what is majority, as overlaps everything else.

But if somebody has emulated the source FB evo radar charts or the infographic political chart but done this in Excel2003 with formula, I'd love to see that.
 

Attachments

  • ChartsYesNoRadarAlt#.xls
    230 KB · Views: 6
Try the attached file:

XLSX is the 2007+ Excel file version
XLSM is the 2007+ Excel file version that also contains macros
 

Attachments

  • ChartsYesNoRadarAlt.xls
    124 KB · Views: 5
Try the attached file:

XLSX is the 2007+ Excel file version
XLSM is the 2007+ Excel file version that also contains macros

Hi Hui, looks greats & opens in Excel2003, but.....

MACRO edit needed, "if" combatible VB alternative available!
Earlier XL version prompts errors, Intersect if ignored, prompted errors both SeriesCollection, then ActiveCell.

If Interact not ignored, then on using Scrollbar, Radar Chart segments/colour do not change.

Run-time error '438' = Object does support this property or method.
Run-time error '91' = Object variable not set.

'If Intersect(ActiveCell, Range("B10:L14", "A8")) Is Nothing Then Exit Sub

Run-time error '438': .SeriesCollection(Doughnut).Points(Segment).Format.Fill.ForeColor.RGB = RGB(46, 117, 182)

Run-time error '438': .SeriesCollection(Doughnut).Points(Segment).Format.Fill.ForeColor.RGB = RGB(255, 255, 255)

Run-time error '91':
ActiveCell.Select

1st Alternative activecell didn't work either: Xapp.activecell.value.
 
This version fixes those errors for pre-Excel 2007 versions
Remarkably it stills runs in Excel post 2007
 

Attachments

  • ChartsYesNoRadarAlt.xls
    106.5 KB · Views: 7
This version fixes those errors for pre-Excel 2007 versions
Remarkably it stills runs in Excel post 2007

Well done Hui!

Concise macro, very effective data visualisation, and considering how much interest the source article has made in several years, that's the 1st working example I've seen that works as intended. Perhaps many couldn't understand what original dataset looked like. I've moved category labels around to look more consistent.

Q: On the VB front, with newer versions of Excel, do you think there is less need for VB, since newer more advanced functions can emulate the desired effect/results?

For example rarely see a spreadsheet with Userform or Macrobuttons these days, personally I like Dashboards with my edit of Index Match & Countif, to keep sorts segregated from master data.
 

Attachments

  • ChartsYesNoRadarAlt#FBevo.xls
    103.5 KB · Views: 4
I use VBA more and more every day, because it increases my flexibility and scope of what can be achieved.
 
Hi Hui.

As your file edit demonstrates, can't imagine formula edit equivalent for this!

Formula & VB expertise, that's an interesting 1, here's some rhetorical thoughts on that, think currently capable people find out for themselves, due to few clear/concise approaches with working examples, that is lacking in availability for persons wanting to learn, and even if preferred major techniques were well documented, few persons have required cognitive skills and patience, for what is a creative process that normally requires good understanding of possible dataset trends and majorities.

On that premise, that many UK datasets are proprietary, and few industry professionals have proficiency to make XL Dashboards, simply because they lack knowledge > skills > competencies in utilising Data Validation Combo Boxes/ Defined Names and use in conjunction with Index Match Countif, or your preferred method to identify fields/rows that meet designated criteria.

Most Dashboard websites, rarely touch on the subject of their how to approach. Instead there is a mass quantity of images and lengthy verbose qualitative comments on data structure & maximising data visualisation impact, etc. In other words come on my training course & buy my templates.

Now for example the migration dashboards challenge, same datasets, and many different approaches, many innovative and impressive approaches, but many their formula approach wouldn't be straightforward to implement on a wider scale, I'd guess they were devised by ex accountants whose perspectives revolve around financial statistics, their typical good approaches are: DV boxes with SumProduct/Offset/Index, or lesser approaches using Dv boxes with Index & Defined Names/Ranges.

Hence if it is hard for persons to comprehend FORMULA appraches, then VB will just be another mystery for them, and many professionals consider themselves visionaries, not facilitators, and their mindset won't be they can't do that, instead it will be, we'll get somebody in to do that etc!


This isn't unique to the IT Industry many industries have skills/competencies issues, normally because courses/qualifications are generic prepatory training, that isn't specific to certain business and their equipment, and within the Business they won't already be experts, and those that are, like to keep that knowledge to themselves, partly for intellectual ownership, and partly because they don't want an abundance of potential replacements, that's unspoken obviously.

For example my profession was the Engineering/Construction Industry, and international industrial businesses had real concerns, that if they needed staff with certain skills sets they didn't not know which of their staff could be used for new projects, and if persons already in the industry met their criteria, or if those entering the industry 'really' had the capability/competencies required to be deemed skilled enough to be of use, hence businesses & individuals were either assessed or training was identified, through audits and/or questionaires, and summarised, see attached file, its a Skills/Competencies summary.
 

Attachments

  • Skills Select#.xls
    921 KB · Views: 6
Final end game version attached! cleaned up tables/textboxes that were distracting.

RADAR SEGMENT CHART TYPE STYLE, data is mock/contrived just to demonstrate.

VB provides laying of BLANKS & COLOURS via YEAR COMBO, thanks Hui.
 

Attachments

  • ChartsYesNoRadarAlt#FBevoVB.xls
    103.5 KB · Views: 4
Back
Top