+ Reply to Thread
Results 1 to 5 of 5

Salary Range before & after Median

  1. #1
    Registered User
    Join Date
    10-31-2020
    Location
    India
    MS-Off Ver
    MS Office 2016
    Posts
    2

    Salary Range before & after Median

    Hi Friends,

    I am confused / lost working on Charts to show Salary Ranges with a Median line as center point, which shows data after / before Median line. I found 2 charts which suits my requirement but unable to get it in excel with the complex data I have. Not sure how/where to start, please suggest a way to get the output.

    Attaching both Excel file & image found in google:

    from Excel file:
    Internal Median tab has the summary view of data, which i want to present it in a form of chart
    1. Column E to M has Salary Range
    2. Column M to N has Headcount Data Value which to be displayed on chart
    3. Ideal Median (Column I) is the break through for each of the Role & Grade - means, this column will be the mid-point where I need to show salary range (Column E to M) before / after value
    4. I will be using data validation to select Designation, basis which the Chart will change with Role, Grade and Salary Range with Headcount.

    Images attached here is an example which I found, expecting something similar to these images or better than this.

    Please help me with this requirement and suggest me if we have a better way to represent the data.


    Eathan
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,310

    Re: Salary Range before & after Median

    Welcome to the forum

    In the first part of your question you ask for: "Charts to show Salary Ranges with a Median line as center point, which shows data after / before Median line."

    From your "Workings" worksheet I have created a Pivot Chart sourced from your "Workings" worksheet that looks like this:

    eathan_boss20gamer.png

    The chart as depicted above shows CTC (salary) as blue bars for all employees that are grade M5 with a role of "Senior Developer". The median for that group is the red line. The filters allow you to quickly select and chart a different set of grades and roles and designations.

    An update to your workbook is attached.

    Hopefully this is of some use - I wasn't really clear on the rest of your request.
    Attached Files Attached Files
    Last edited by GeoffW283; 11-01-2020 at 06:28 PM.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu

  3. #3
    Registered User
    Join Date
    10-31-2020
    Location
    India
    MS-Off Ver
    MS Office 2016
    Posts
    2

    Re: Salary Range before & after Median

    Thanks for the chart Groff. Will use this chart in other dashboard.

    My requirement is different from this excel file / thread. please excuse me if my question was not clear, here are the details for understanding:

    1. I want this chart to be on Designation - Role - Grade - Internal Median - CTC/Salary Range, where only designation will be picked from Data validation list / slicer
    eg: Developer - Sr. Developer - will have ideal median
    2. When we select a Designation(Developer), all available Roles of this group should be displayed with their salary ranges like Role - Grade - Ideal Median - CTC / Salary
    3. CTC are grouped into Percentile to show Salary Range from 10th percentile to 90th Percentile in chart (as mentioned in Internal Median sheet)
    4. In chart, I need to group CTC into the percentile positions (P10,P25,P50,P75 etc) to show salary range of a role under various grades with a break through of Ideal Median
    eg: Sr. Developer will have 5 level of Grade with a common Ideal Median, each Grade level salaries are positioned in P10, P25, P50, P75 etc however there Ideal Median value will not be same as P50 of a grade, hence I need a point / line in chart which shows that their salary range is above Ideal Median or below Ideal Median Level
    5. Data Labels on chart will be headcount of each grade from that P10,P25,P50 etc (as mentioned in Internal Median sheet)
    6. The images shared in my previous thread are something similar to this requirement, each of the percentile group is colored and easy to understand

    Hope I am clear on my requirement.

    Eathan

  4. #4
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,310

    Re: Salary Range before & after Median

    Per my response to your PM, the usual reason for lack of responses is unclear requirements - especially if the requirements are complex. Few people will want expend significant effort on a guess at a solution if there is a high probability that it won't meet your real need.

    Here's some questions, the answers to which might help you to clarify your requirements.
    1. How many charts are you expecting to develop? - are you expecting everything you ask for to fit on a single chart or will there be multiple charts? If the latter then describe each chart separately.
    2. For each chart, what specific worksheet & column maps to the x-axis?
    3. For each chart, what specific worksheet & column(s) are to be plotted on the y-axis?
    4. Do the columns to be charted all exist or do you expect additional columns to be computed, if so what are they and how are they calculated?
    5. You have grabbed a couple of charts from the Web, one looks like a horizontal stacked bar, the other looks like a waterfall chart - which do you want?
    6. Can you provide a specific mapping from all of the data depicted on your chosen chart to your specific data columns?
    7. Alternatively, can you, in some drawing program, sketch exactly what you have in mind and annotate the sketch with the exact mapping your specific data columns.
    8. Can you think of a simpler subset of your requirements that would represent a first step towards what you ultimately want - something that could then be built on step by step? That way you could at least show some progress in the short term.

    Finally - when you provide column references then make sure they are accurate! For example in your original post #1 you reference Columns E to M - I think it should be E to L, also you reference Columns M to N - I think it should be M to T.

    In summary, hopefully, if you can clarify your needs further or if you can specify a simpler first step, then you may attract the interest of someone with more in-depth charting knowledge than I can bring to bear.

    I hope this helps.

  5. #5
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,310

    Re: Salary Range before & after Median

    You said:
    In simple, my leaders want to see Salary range of all employees at each Role, Grade and their percentile position in a chart with a ideal median as a line (where they can see how many employees are above & below ideal median), beyond this I don't know how to explain.

    Here's the best I can do: It's another pivot chart.

    eathan_boss20gamer_1.png

    The orange bars show the salary range for all employees for each role. You can filter on grade to see the specific salary ranges for one or more grades.
    The red bars are the "Ideal median" taken from Workings!I:I. I needed to add a new column to Workings!J:J called "MaxMinusMin" in order to construct the chart.
    Attached Files Attached Files

+ 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. Adding Salary Increase to already prorated salary amount based on a salary increase date
    By Excelhelppleasethank in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-09-2019, 03:43 PM
  2. Replies: 7
    Last Post: 08-29-2018, 08:28 AM
  3. Replies: 1
    Last Post: 11-22-2017, 05:27 AM
  4. [SOLVED] Need formula for prorating monthly salary based on hire/fire date and annual salary.
    By Excel_Help_Pls in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-06-2015, 12:56 AM
  5. Median Indirect: Find median in range and bring back adjacent cells
    By Keelin in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-27-2014, 08:31 AM
  6. Replies: 2
    Last Post: 05-27-2014, 01:17 AM
  7. Replies: 8
    Last Post: 09-06-2013, 05:57 AM

Tags for this Thread

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