Calculate the average cooperation time in months for those who are still with the company (do not have value on column leave date) as of 16/08/2024, categorized by their respective levels.
📌 Challenge Details and Links
Challenge Number: 108
Challenge Difficulty: ⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Avg Cooperation Time! with Power Query
Power Query solution 1 for Avg Cooperation Time!, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.Group(
Source,
"Level",
{
"AVG Cooperation time",
each List.Average(
List.Transform(
List.Select(Table.ToRows(_), each _{3} = "-"),
each
let
e = #datetime(2024, 08, 16, 0, 0, 0)
in
(Date.Year(e) - Date.Year(_{2})) * 12 + Date.Month(e) - Date.Month(_{2})
)
)
}
)
in
SPower Query solution 2 for Avg Cooperation Time!, proposed by Brian Julius:
let
Source = Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name = "rng"]}[Content]),
Filter = Table.SelectRows(Source, each (try Date.From([#"Leave date"]) otherwise 1) = 1),
AddMonths = Table.AddColumn(
Filter,
"Months",
each [
a = #date(2024, 8, 16),
b = Date.Year(a) - Date.Year([#"Employee Date"]),
c = Date.Month(a) - Date.Month([#"Employee Date"]),
d = (b * 12) + c
][d]
),
Group = Table.Group(AddMonths, {"Level"}, {{"Avg Cooperation", each List.Average([Months])}})
in
GroupPower Query solution 3 for Avg Cooperation Time!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Avg = Table.AddColumn(
Table.SelectRows(Source, each [Leave date] = "-"),
"A",
each Duration.Days(#date(2024, 08, 16) - Date.From([Employee Date]))
),
Sol = Table.Group(Avg, {"Level"}, {{"[AVG Cooperation]", each List.Average([A]) / 30}})
in
SolPower Query solution 4 for Avg Cooperation Time!, proposed by Kris Jaganah:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.SelectRows(S, each ([Leave date] = "-")),
B = Table.TransformColumns(
A,
{"Employee Date", each (Number.From(#date(2024, 08, 16)) - Number.From(_)) / 30.5}
),
C = Table.Group(B, {"Level"}, {"AVG Cooperation in Months", each List.Average([Employee Date])})
in
CPower Query solution 5 for Avg Cooperation Time!, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.Group(
Source,
"Level",
{
"AVG Cooperation time",
each List.Average(
List.Transform(
List.Select(Table.ToRows(_), each _{3} = "-"),
each
let
e = #datetime(2024, 08, 16, 0, 0, 0)
in
(Date.Year(e) - Date.Year(_{2})) * 12 + Date.Month(e) - Date.Month(_{2})
)
)
}
)
in
SSolving the challenge of Avg Cooperation Time! with Excel
Excel solution 1 for Avg Cooperation Time!, proposed by محمد حلمي:
=LET(c,
C3:C12,
u,
UNIQUE(
c
),HSTACK(u,
MAP(u,
LAMBDA(a,AVERAGE(FILTER(
DATEDIF(
D3:D12,
"8/16/2024",
"m"
),(c=a)*(E3:E12="-")))))))Excel solution 2 for Avg Cooperation Time!, proposed by Oscar Mendez Roca Farell:
=LET(u,
UNIQUE(
C3:C12
),
HSTACK(u,
MAP(u,
LAMBDA(a,
AVERAGE(FILTER(45520-D3:D12,
(C3:C12=a)*(E3:E12="-"))/30)))))Excel solution 3 for Avg Cooperation Time!, proposed by Julian Poeltl:
=MAP(J3:J4,
LAMBDA(A,
AVERAGE(DATE(
2024,
8,
16
)-FILTER(D3:D12,
(C3:C12=A)*(E3:E12="-")))))Excel solution 4 for Avg Cooperation Time!, proposed by Kris Jaganah:
=GROUPBY(C3:C12,
(DATE(
2024,
8,
16
)-D3:D12)/30,
AVERAGE,
0,
0,
,
E3:E12="-")Excel solution 5 for Avg Cooperation Time!, proposed by Imam Hambali:
=LET( a,
FILTER(
HSTACK(
C3:C12,
"2024-08-16"-D3:D12
),
E3:E12="-"
), VSTACK(
HSTACK(
"Staff ID",
"AVG Cooperation in month"
),
GROUPBY(
TAKE(
a,
,
1
),
TAKE(
a,
,
-1
)/365*12,
AVERAGE,
0,
0
)
))Excel solution 6 for Avg Cooperation Time!, proposed by Sunny Baggu:
=MAP(
UNIQUE(
C3:C12
), LAMBDA(a, AVERAGE(
TOCOL(
IF(
(C3:C12 = a) * (E3:E12 = E3), YEARFRAC(
--D3:D12,
DATE(
2024,
8,
16
)
) * 12, x
), 3
)
)
)
)Excel solution 7 for Avg Cooperation Time!, proposed by Alejandro Campos:
=LET(
s,
SUBSTITUTE(
FILTER(
C3:E12,
E3:E12 = "-"
),
"-",
45520
), a,
HSTACK(
TAKE(
s,
,
1
), (BYROW(
--DROP(
s,
,
1
),
LAMBDA(
x,
INDEX(
x,
,
2
) - INDEX(
x,
,
1
)
)
) / 30)), GROUPBY(
TAKE(
a,
,
1
),
TAKE(
a,
,
-1
),
AVERAGE,
0,
0
))Excel solution 8 for Avg Cooperation Time!, proposed by Eddy Wijaya:
=LET( d,
B3:E12, s_d,
"8/16/24", a_d,
FILTER(
d,
TAKE(
d,
,
-1
)="-"
), la_d,
CHOOSECOLS(
a_d,
2
), diff,
BYROW(
CHOOSECOLS(
a_d,
3
),
LAMBDA(
r,
s_d-r
)
), HSTACK(
UNIQUE(
la_d
),
MAP(
UNIQUE(
la_d
),
LAMBDA(
m,
AVERAGE(
FILTER(
diff,
la_d=m
)
)/30
)
)
)
)Excel solution 9 for Avg Cooperation Time!, proposed by Mey Tithveasna:
=MAP(J3:J4,
LAMBDA(j,
AVERAGE(FILTER(
(DATEDIF(
D3:D12,
DATE(
2024,
8,
16
),
"m"
),
(E3:E12="-")*(C3:C12=j)))))Excel solution 10 for Avg Cooperation Time!, proposed by Milan Shrimali:
=LET(
A,
A3:E12,
mnths,
BYROW(
Filter(
FILTER(
a,
CHOOSECOLS(
a,
3
)=""
),
{1,
1,
0}
),
lambda(
x,
hstack(
x,
DATEDIF(
choosecols(
x,
2
),
date(
2024,
8,
16
),
"m"
)
)
)
),
map(
unique(
choosecols(
mnths,
1
)
),
lambda(
x,
hstack(
x,
AVERAGE(
filter(
choosecols(
mnths,
3
),
choosecols(
mnths,
1
)=x
)
)
)
)
)
)Excel solution 11 for Avg Cooperation Time!, proposed by Nicolas Micot:
=LET(_levels;
UNIQUE(
C3:C12
);
MAP(_levels;
LAMBDA(l_level;MOYENNE(SI((E3:E12="-")*(C3:C12=l_level);
(DATEVAL(
"16/08/2024"
)-D3:D12);
""))/(365/12))))Excel solution 12 for Avg Cooperation Time!, proposed by Pieter de B.:
=LET(u,
UNIQUE(
C3:C12
),
HSTACK(u,
MAP(u,
LAMBDA(a,
AVERAGE(YEARFRAC(FILTER(D3:D12,
(C3:C12=a)*(E3:E12>"")),
45520,
3)*12)))))Excel solution 13 for Avg Cooperation Time!, proposed by Rick Rothstein:
=MAP(J3:J4,
LAMBDA(x,
AVERAGE(FILTER(DATEDIF(
D3:D12,
DATE(
2024,
8,
16
),
"m"
),
(C3:C12=x)*(E3:E12<>"-")))))Excel solution 14 for Avg Cooperation Time!, proposed by Zbigniew Grodzicki:
=AVERAGE(FILTER(DATEDIF(
$D$3:$D$12,
TODAY(),
"M"
),
($C$3:$C$12=J3)*($E$3:$E$12="-")))Solving the challenge of Avg Cooperation Time! with Python
Python solution 1 for Avg Cooperation Time!, proposed by Konrad Gryczan, PhD:
import pandas as pd
from datetime import datetime
path = "CH-108 AVG Cooperation time.xlsx"
input_data = pd.read_excel(path, usecols="B:E", skiprows=1)
test_data = pd.read_excel(path, usecols="J:K", skiprows=1, nrows=2)
result = input_data[input_data["Leave date"] == "-"].copy()
result["Difference"] = (datetime(2024, 8, 16) - result["Employee Date"]).dt.days / 30.4375
result["mean_difference"] = result.groupby("Level")["Difference"].transform("mean")
result = result[["Level", "mean_difference"]].drop_duplicates()
print(result)
# Level mean_difference
# 0 Expert 56.542094
# 2 Managerial 56.640657Solving the challenge of Avg Cooperation Time! with Python in Excel
Python in Excel solution 1 for Avg Cooperation Time!, proposed by Alejandro Campos:
import datetime
df = xl("B2:E12", headers=True)
df['Employee Date'] = pd.to_datetime(df['Employee Date'], format='%d/%m/%Y')
current_date = datetime(2024, 8, 16)
df_active = df[df['Leave Date'] == '-']
df_active['AVG Coperation in month'] = (current_date - df_active['Employee Date']).dt.days / 30
average_cooperation = df_active.groupby('Level')['AVG Coperation in month'].mean().reset_index()
average_cooperationSolving the challenge of Avg Cooperation Time! with R
R solution 1 for Avg Cooperation Time!, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/CH-108 AVG Cooperation time.xlsx"
input = read_excel(path, range = 'B2:E12')
test = read_excel(path, range = 'J2:K4')
result = input %>%
filter(`Leave date` == "-") %>%
mutate(cooperation = interval(ymd(`Employee Date`), ymd("2024/08/16")) / months(1)) %>%
summarise(avg_cooperation = mean(cooperation), .by = Level)
result
# Level avg_cooperation
#
# 1 Expert 56.5
# 2 Managerial 56.6 Solving the challenge of Avg Cooperation Time! with Google Sheets
Google Sheets solution 1 for Avg Cooperation Time!, proposed by Peter Krkos:
PowerQuery Solution:
https://docs.google.com/spreadsheets/d/1zR5IZLz8OT76vhaPEHfsPrw8-RDKnLyyqS49IJjdhFk/edit?usp=sharing