IF using multiple columns and date ranges to calculate Turnover

[Kelli Webb]

Hello,

There is nothing like another challenge before Christmas....

I would love some assistance to create a formula using columns A & B to identify who the turnover is for & what the turnover is.

The calculation then is the Sum of Terminations (Last 12 months) / the Average Headcount (Last 12 months).

The outcome should be as per O17:Z22, highlighted green - I know the formula I have used works, but it isn't robust and if one tab changes the sort for example, this will not be fit for purpose.

If you need anymore information, please don't hesitate to reach out to me.

thank you, and stay safe over the holidays!!

Kelli

 

[Peter Bartholomew]

The attached is based upon a Dynamic Array formula but it makes something of a meal of the problem.

= LET(
  YearStart, EDATE(+Period, -12),
  TerminationRow, XLOOKUP(@Office&@Department, Office&Department, Terminations),
  HeadcountRow,   XLOOKUP(@Office&@Department, Office&Department, HC),
  YrTermination, SUMIFS( TerminationRow, Period, ">"&YearStart, Period, "<="&Period ),
  AvHeadcount, AVERAGEIFS(HeadcountRow, Period, ">"&YearStart, Period, "<="&Period),
  YrTermination / AvHeadcount )

 

[Kelli Webb]

The attached is based upon a Dynamic Array formula but it makes something of a meal of the problem.

= LET(
  YearStart, EDATE(+Period, -12),
  TerminationRow, XLOOKUP(@Office&@Department, Office&Department, Terminations),
  HeadcountRow,   XLOOKUP(@Office&@Department, Office&Department, HC),
  YrTermination, SUMIFS( TerminationRow, Period, ">"&YearStart, Period, "<="&Period ),
  AvHeadcount, AVERAGEIFS(HeadcountRow, Period, ">"&YearStart, Period, "<="&Period),
  YrTermination / AvHeadcount )

Thank you Peter, this is Perfect!
I wish you a very Safe & Happy Holidays!!

take care, Kelli

 

[p45cal]

The department names in cells B6:B7 are different from those in cells B13:B14; is this correct?

 

[Kelli Webb]

The department names in cells B6:B7 are different from those in cells B13:B14; is this correct?

No sorry, good pick up. They should all be the same. I hate trying to simplify..

Kelli

 

[p45cal]

I don't recommend the attached as a solution, it was more of a Power Query exercise for me.
2 Named ranges added HeadCount and Terminations.
Update them with new data (resize if necessary), make sure the Department and Office columns match, then refresh the table at the bottom (Results).
This was more convoluted than I thought it would be because I wanted to retain the real dates information in your tables' headers.