Salary chart with specific data point within range

[melifreeman]

I'm looking to create a salary chart for all my employees. I'd like to be able to show their specific location on an area chart representing salary range for that title.

salary data:

employee, title, salary
joe, coach, 72000
mike, coach, 55000
kelly, mktg, 65000
steve, rctg, 88000
jane, writer, 30000
michelle, em, 50000
anan, em, 55000

salary band

title, min, avg, max
coach, 40000, 60000, 90000
mktg, 45000, 65000, 85000
rctg, 65000, 80000, 95000
writer, 30000, 60000, 80000
em, 50000, 60000, 75000

Note: I'd like to be able to show where they are falling within the range as a moving overlay of sorts (so the data for the bands stays the same, but their data moves as their wage increases (bonuses and such). I've seen it done in column form (I've attached a sample of that) but I'm having issues figuring out how to make this happen...your help would be greatly appreciated!

 

[ms_excel_recal_or_die]

Hi @melifreeman

I believe this is what you're looking for: (see image below)

If you're going to add more employee salary data, you add it on the first gray table. (name: employees)(be careful the x-axis column of this table as is, it has a formula)
If you're going to add more position types or position tittles, then you add them on the second gray table. (name: salary_bands) (the last two columns have formulas)

For file download, go to this link: 001 mr_excel_help.xlsx

Hope it helps you.

Best,
Carlos