How to calculate average promotion pace

**Nipius** · 10-18-2017, 05:46 AM

Hi all!

I have been playing around with my question for some days, but get stuck at the end, as each outcome has some implications.

I want to answer the following question. Did the average promotion pace increase over the past years? I have the promotion dates for a group of large set of employees between 1998 and 2017. So based on that, I know how long it took before they reached a certain work level. What I have done, is to make three groups, with several buckets. The groups are based on the year they entered the starting work level ("1998-2003" / "2004-2009" / "2010-2015"). The buckets are "0.0 to 1.5", "1.5 to 3.0", et cetera. An example. If an employee became the starting work level in 1999 and it took this persoon 8 years to promote, (s)he would fall in the bucket "7.5 - 9.0".

My current outcome shows that 45% of the group "2010-2015" are in the "3.0-4.5" bucket, compared to 20% of the "1998-2003" bucket. The issue I face however, is that this latter group ("1998-2003") had a bigger time span to promote to the desired work level, whereas the "2010-2015" group will have quite some employees still in the process of reaching the final work level. They are thus excluded from my output and logically will have only a extremely small group of employees in the "7.5 - 9.0" bucket.

Is there a way to get my desired output, where I actually compare apples with apples?

**AliGW** · 10-18-2017, 05:53 AM

Will you please attach a sample Excel workbook? Nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

**Nipius** · 10-18-2017, 07:22 AM

Thanks, AliGW. I attached an Excel forum with dummy data :-)

**AliGW** · 10-18-2017, 07:34 AM

I've moved the thread to the charting and pivots section, which seems more appropriate based on your sample file.

**MrShorty** · 10-18-2017, 12:44 PM

I'm not very good with pivot tables, so this may not be the best approach, but it seemed to work. First step to charting a given set of data is to compute the data in the spreadsheet.

1) Create pivot table with 1a) Time 1 to 3 (Grouped) as the row labels, 1b) Grouped year work level 1 as the column labels, and 1c) Count of time 1 to 3 (grouped) as the value field setting. Here's a quick intro to creating pivot tables if you need help: http://www.excel-easy.com/data-analy...ot-tables.html
2) Compute ratios in helper columns adjacent to the pivot table =B5/B$12 copy down and across (row 12 was the grand total row).
2a) It seems like someone more skilled at pivot tables would know how to make this calculation part of the value field setting of the main pivot table, but I don't.
3) Create column chart using helper columns.

Does that help at all?