+ Reply to Thread
Results 1 to 7 of 7

Is there a better way to show this? (Stacked Column - want to show quantity and ratio)

  1. #1
    Registered User
    Join Date
    06-29-2017
    Location
    USA
    MS-Off Ver
    Office 2016
    Posts
    76

    Is there a better way to show this? (Stacked Column - want to show quantity and ratio)

    Hi!

    Please see the attached file. But basically I have some sample data with number of times 6 people have been early for class, on time, or late for class.

    The most important thing I want to show is the % each person has been on time and early (just one value, just the sum of on time and early divided by the total). But since there is such discrepancy between the total volumes of each person, I also want to show somehow the total quantities. So I want the viewer to be able to see essentially how often each person has been late but also get a sense of the sample size of each person.

    So using the data I have a sample chart in the attached file. It kinda shows what I want using a second series which is a line chart, but is there a better way to show this? I feel like people usually associate line charts with the passage of time which doesn't make sense here. Also since showing the % late is the most important thing, it doesn't pop out as such since it's a line on the second series. Not sure...

    Any advice?

    Thanks!
    Attached Files Attached Files
    Last edited by Katie620; 08-21-2018 at 02:15 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Is there a better way to represent this data?

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    06-29-2017
    Location
    USA
    MS-Off Ver
    Office 2016
    Posts
    76

    Re: Is there a better way to show this? (Stacked Column - want to show quantity and ratio)

    Does what I'm asking make sense? I thought about a pie chart with quantities, and then somehow (second series maybe) put the % in each slice that represents each person's late % but I don't know how clear that would be since it's not really showing the pie %'s it's showing something else.

  4. #4
    Registered User
    Join Date
    06-29-2017
    Location
    USA
    MS-Off Ver
    Office 2016
    Posts
    76

    Re: Is there a better way to show this? (Stacked Column - want to show quantity and ratio)

    Bump Any thoughts?

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Is there a better way to show this? (Stacked Column - want to show quantity and ratio)

    After another 24 hours, I would guess that no one has any better ideas. What you have seems to work pretty well. If you need a couple of alternatives:

    1) Perhaps drop the stacked column chart, keep only the line chart, and add a data label that shows the volume for each point as text.
    2) If you are worried about the "line" chart giving a sense of "time", format the line series to be markers without line.
    3) For something really different, you could use a bubble chart -- where volume is the bubble size, on time rate is the y values, and person number is the X values. A large bubble indicates those with large volume and a small bubble indicates those with low volume. The main problem with a bubble chart might be that it becomes difficult with a large bubble to know exactly what on time rate is indicated.

    IMO, what you have does a decent job of showing both on-time rate and volume -- as long as the reader can remember that the line chart on time rate goes with the secondary axis. If your target audience can handle it, I might leave it as is.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Is there a better way to show this? (Stacked Column - want to show quantity and ratio)

    This is small enough data without any change over time.
    I probably won't use chart to visualize this data, unless there is another dimension that can show some insight into the data.


    Perhaps, Data bar/conditional format on % column to serve as KPI indicator.


    0.JPG
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  7. #7
    Registered User
    Join Date
    06-29-2017
    Location
    USA
    MS-Off Ver
    Office 2016
    Posts
    76

    Re: Is there a better way to show this? (Stacked Column - want to show quantity and ratio)

    Thanks everyone! Will use your suggestions as needed based on audience response. Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. What's the ideal way to represent this data in a chart?
    By Katie620 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-11-2018, 08:32 PM
  2. complicated macro to copy original data and represent in different format in NEW DATA
    By swilson2006 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 03-17-2018, 10:24 AM
  3. Best way to represent this data in excel chart
    By masimo in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-17-2017, 08:37 PM
  4. how to best represent data
    By NJ007 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-14-2013, 10:19 PM
  5. Best way to represent this data
    By pg300 in forum Excel General
    Replies: 9
    Last Post: 08-22-2011, 04:07 AM
  6. best way to represent data in Chart(graph)
    By maximpinto in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-11-2010, 02:36 AM
  7. Can't get chart to represent data correctly - 03 or 07
    By indi in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-22-2009, 12:26 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1