1

I would like to replicate

enter image description here

in Excel, which groups hundreds of occupations under 12 major job categories, and plots them according to number of jobs and probability of computerisation.

I currently have several hundred occupations in a column Excel, with three columns showing the following data

enter image description here

  1. Major job category
  2. Probability of computerisation (0-100%)
  3. Number of jobs ('000)

I am unsure how to proceed from this point, as I am unfamiliar with advanced Excel functions.

2
  • What exactly do you need to calculate? Sum of number of jobs by category is clear. For computerization, do you need just average by category, or the average need to be weighted by number of jobs too? Oct 28, 2015 at 23:56
  • It's a bit difficult to explain, but say there are two types of managers, the first type having 10,000 workers and a 10% chance of computerization, the second having 5,000 workers and a 70% chance. Rather than the average, I want to plot both types of managers separately on the chart, showing their chance of computerization in the x-axis and employment levels in the y-axis.
    – user515246
    Oct 29, 2015 at 0:22

1 Answer 1

0

First approach would be to summarize your data with a pivot chart:

  • select your data, then go to Insert - PivotTable - PivotChart
  • set:
    • major category to column labels
    • computerisation to row labels
    • employment to values (calculate it's average)
  • change chart type to stacked area

Depending on size of your data and accuracy (number of decimals) of computerisation data, this could give you a nice chart.

For me, on 100 completely random data, with 3 digit accuracy of computerisation it isn't really nice:

enter image description here

Next step is to group (bin) your data

You need to group data in order to get enough amount of information in each group to show a realistic chart (e.g. group together companies with computerisation 0.015 - 0.025).

As probability is going from 0 to 1, a very easy grouping is just to round them, entering a new column, you can round to

  • 3 decimals with =ROUND([computerisation],3)
  • 2 decimals ending to 0 or 5 (0, 0.05, 0.10, 0.15...) with =ROUND([computerisation]/5,2)*5

Using the second grouping I could achieve a more nice chart:

enter image description here

Number of groups is really depends on your data (number records, distribution across bins, groups, company sizes), probably you need to play a bit around to see what gives the best picture.

You must log in to answer this question.