[Peter Mosbrenner]

Good answer?Vote up

Up

0

::

Hi community,
Don’t tell me “just google the answer” – I have tried and I am overwhelmed by the sheer number of possible solutions. I just want a (simple, if possible) solution that really works.
My problem is the following: I have some data in our system (employee status – either “active” or the date when they left) and some other relevant information in an excel list (in my case: engagement scores on team level from the engagement survey provider). How can I link the two? I would like to see if there is a pattern e.g. higher employee turnover in teams that had low engagement scores in the past two years.
Thanks, Peter

[Anonymous]

Good answer?Vote up

Up

0

::

Hi Peter,
Interesting topic – and I am sure you are not alone with this. Your challenge actually consists of two parts:

• a) you have two data sets (“lists”) with a different level of granularity (one at individual level, one at team level – because I would be very surprised if you had engagement data on an individual level) and
• b) the two sets are apart and you need them linked (or “joined”).

The easiest solution would of course be to ask somebody from your analytics team (if you have such a team in your organisation) to do this for you: calculating the employee turnover rate per team and then link it to the engagement scores of the past years (also on team level). Mind you that you will need to live with the fact that the result will not be 100% precise since you are comparing engagement scores of teams (sounds like the survey is a recent one) with the turnover of teams that include individuals that are not included in the survey (because they left before). But that’s just small differences which can be ignored IMHO.
Since you ask this question here, I assume you don’t have the luxury of having a helpful analytics professional (who happens to have time for your question).

So – how do you solve the question yourself?
I assume that you have a list with the engagement scores (probably the averages or median) per team. And – as you told us in your question – you have a list of all employees of last year with a status either “active” or the date when they left. There are a lot of different ways to get this done – e.g. using pivot tables – but below in the attachment I show you a very simple one relying on just two formulas:
– one formula helps you calculate the employee turnover per team
– the second formula links the list of engagement scores with the list of the employee turnover (using VLOOKUP; you could also use XLOOKUP)
In the example we have just two teams with just a handful of employees, but the principle can be applied to any number. Please note that we do not distinguish between the reasons for leaving (voluntary vs involuntary due to restructuring or performance). In practice I would highly recommend to only look at voluntary leavers when analysing it connected to engagement.
Hope this helps – let me know if there are any further questions. If you want to get more help, a 1:1 Coaching with one of us might work best for you – get in touch!

Best,
F-Top Coach / Ursula

[Author]

Posts