Calculate the mission’s income, where the daily rate starts at $1 on the first day and increases by $1 for the next continuous days (the rate for the third day in the mission is 3$). If there’s an interruption between mission days, the rate resets to $1. Weekends are excluded, so missions continuing from Friday to Monday count as consecutive days. For example, person C earned $4: $1 for the mission on 30/01/2024, another $1 for 07/02/2024, and $2 for 08/02/2024.
📌 Challenge Details and Links
Challenge Number: 4
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Advanced Calculation with Excel
Excel solution 1 for Advanced Calculation, proposed by Bo Rydobon 🇹🇭:
=LET(
d,
B3:B17,
p,
C3:C17,
u,
SORT(
UNIQUE(
p
)
),
HSTACK(
u,
MAP(
u,
LAMBDA(
v,
LET(
a,
FILTER(
d,
p=v
),
SUM(
SCAN(
0,
NETWORKDAYS(
DROP(
VSTACK(
0,
a
),
-1
),
a
)=2,
LAMBDA(
a,
v,
a*v+1
)
)
)
)
)
)
)
)Excel solution 2 for Advanced Calculation, proposed by محمد حلمي:
=LET(c,
C3:C17,
u,
UNIQUE(
c
),
SORT(HSTACK(u,MAP(u,
LAMBDA(a,
LET(j,
FILTER(
B3:B17,
c=a
),SUM(SCAN(0,
SEQUENCE(
ROWS(
j
)
),
LAMBDA(i,
v,(INDEX(
j,
v
)=WORKDAY(
INDEX(
VSTACK(
0,
j
),
v
),
1
))*i+1)))))))))Excel solution 3 for Advanced Calculation, proposed by Kris Jaganah:
=LET(a,
SORT(
B3:C17,
2
),
b,
TAKE(
a,
,
1
),
c,
DROP(
a,
,
1
),
d,
VSTACK(
@b,
DROP(
b,
-1
)
),
e,
VSTACK(
@c,
DROP(
c,
-1
)
),
g,
SCAN(0,
(c=e)*(b-d),
LAMBDA(
x,
y,
IF(
y=1,
x+1,
1
)
)),
h,
UNIQUE(
c
),
HSTACK(h,
MAP(h,
LAMBDA(z,
SUM((c=z)*g)))))Excel solution 4 for Advanced Calculation, proposed by John Jairo Vergara Domínguez:
=GROUPBY(
C3:C17,
B3:B17,
LAMBDA(
b,
SUM(
1,
SCAN(
1,
DROP(
b,
1
)=DROP(
WORKDAY(
b,
1
),
-1
),
LAMBDA(
a,
v,
1+a*v
)
)
)
),
,
0
)Excel solution 5 for Advanced Calculation, proposed by John Jairo Vergara Domínguez:
=LET(
p,
C3:C17,
u,
UNIQUE(
p
),
SORT(
HSTACK(
u,
MAP(
u,
LAMBDA(
x,
LET(
f,
FILTER(
B3:B17,
p=x
),
SUM(
1,
SCAN(
1,
DROP(
f,
1
)=DROP(
WORKDAY(
+f,
1
),
-1
),
LAMBDA(
a,
v,
1+a*v
)
)
)
)
)
)
)
)
)Excel solution 6 for Advanced Calculation, proposed by Surendra Reddy:
=LET(
a,
SORTBY(
B3:C17,
C3:C17
),
b,
INDEX(
a,
,
2
),
u,
UNIQUE(
b
),
HSTACK(
u,
MAP(
u,
LAMBDA(
r,
LET(
f,
FILTER(
a,
b=r
),
SUM(
SCAN(
0,
VSTACK(
1,
DROP(
INDEX(
f,
,
1
),
1
)-DROP(
INDEX(
f,
,
1
),
-1
)
),
LAMBDA(
x,
y,
IF(
y=1,
x+1,
1
)
)
)
)
)
)
)
)
)