+ Reply to Thread
Results 1 to 2 of 2

What's the ideal way to represent this data in a chart?

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

    What's the ideal way to represent this data in a chart?

    Hi! I have three columns of data:
    • % Pay to Market Rate (Employees salary divided by a set market rate for that position)
    • Years of experience (1 year, 2 year, 3 year, 4 year, 5 year) (where each employee is grouped into each category)
    • Still with company (either Yes or No)


    I have 2000 rows, where each row is an employee.

    If I had the exact amount of time each person had been with the company (so the X axis might be 60 months instead of just 5 years), I could use a scatter plot where the X axis is years of experience and Y axis is % Pay, and I could make the dots either different colors or different sizes to indicate the binary choice of with company or not.

    But the problem is that I only have 5 possible X axis choices so a scatter-plot wouldn't work (would just be 5 vertical lines of dots).

    So my question: Given the data and assuming no additional data could be had is there a way to represent this data? The goal of a visual representation would be so that you could see at what % or what year of experience are most people leaving, so that you could theoretically pay those that are in that 'danger zone' a higher salary to retain them?

    Thanks!

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: What's the ideal way to represent this data in a chart?

    Rather than a chart, I would test a couple of those hypothesis for correlation. Like, I would run a Pearson test on the three variables with each other (%PtMR vs. YoE, %PtMR vs. WC, YoE vs. WC), and then examine those particularly. I suspect that there would be a rough linear relationship between Years-of-Experience and With Company.

    I'd also run %PtMR as a column chart, with the x-axis columns being buckets (70-80%, 80-90%, 90-100%, etc) of pay scale, and the y-axis height being counts of people in that bucket. I'd blindly assume that it would be normally distributed (but it might be bimodal), and further I'd be curious what the average was, etc. If you're already paying above market on average, then just trumpeting that fact might be good for retention.

    Basically I feel like you're asking too many questions of the data to be answered in just one chart.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

+ 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. 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
  2. Replies: 0
    Last Post: 01-12-2016, 03:16 PM
  3. After Loop Completes, Chart doesn't represent new data
    By jrembold in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-18-2015, 08:53 AM
  4. [SOLVED] How to have a graph/chart represent whether or not a cell h as been filled
    By Cities in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-14-2014, 02:40 PM
  5. Is there a simple way or chart to represent multi dimensional data?
    By sreenire in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-04-2012, 11:01 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