Divide the numbers given in column A by 7 and round it up. Divide the result achieved by 7 and round it up. Keep dividing by 7 till you get 1. Get the sum of all these numbers after roundup. Example – For number 124 ROUNDUP(124/7,0) = 18 ROUNDUP(18/7,0) = 3 ROUNDUP(3/7,0) = 1 Result = 18+3+1 = 22
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 101
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Divide by 7 Recursively with Power Query
Power Query solution 1 for Divide by 7 Recursively, proposed by Brian Julius:
https://gorilla.bi/power-query/list-generate/
https://app.enterprisedna.co/app/virtual-events/224?chapter=1&item=330
Power Query solution 2 for Divide by 7 Recursively, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ExpectedOutput = Table.AddColumn(
Source,
"Sum",
each List.Last(
List.Generate(
() => [x = [Number], y = 1],
each [x] > 1,
each [x = Number.RoundUp([x] / 7, 0), y = [y] + x],
each [y]
)
)
)
in
ExpectedOutputPower Query solution 3 for Divide by 7 Recursively, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "Divide7"]}[Content],
#"Added Custom" = Table.AddColumn(
Source,
"Sum",
each List.Last(
List.Generate(
() => [n = Number.RoundUp([Number] / 7), s = n],
each [n] > 1,
each [n = Number.RoundUp([n] / 7), s = [s] + n],
each [s]
)
) + 1
)
in
#"Added Custom"
Easier with List.Sum:
let
Source = Excel.CurrentWorkbook(){[Name = "Divide7"]}[Content],
#"Added Custom" = Table.AddColumn(
Source,
"Sum",
each List.Sum(
List.Generate(
() => [n = Number.RoundUp([Number] / 7)],
each [n] > 1,
each [n = Number.RoundUp([n] / 7)],
each [n]
)
) + 1
)
in
#"Added Custom"
Power Query solution 4 for Divide by 7 Recursively, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.AddColumn(
Source,
"Sum",
each List.Sum(
List.Skip(List.Generate(() => [Number], each _ > 1, each Number.RoundUp(_ / 7, 0)))
)
+ 1
)
in
ResultPower Query solution 5 for Divide by 7 Recursively, proposed by Jan Willem Van Holst:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"NcrBDQAgCEPRXXr2oAgIsxD3X0MkeuvLbwSmoWFht8Agzk1UkN4TpgV3Twy10hTWpDj/+LLewz4=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Number = _t, Sum = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Number", Int64.Type}, {"Sum", Int64.Type}}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Custom",
each
let
s1 = List.Generate(() => [Number], each _ > 1, each Number.RoundUp(_ / 7)),
s2 = List.Sum(s1) + 1 - [Number]
in
s2
)
in
#"Added Custom"Solving the challenge of Divide by 7 Recursively with Excel
Excel solution 1 for Divide by 7 Recursively, proposed by Bo Rydobon 🇹🇭:
=LET(
r,
LAMBDA(
r,
a,
LET(
b,
ROUNDUP(
a/7,
),
b+IF(
b>1,
r(
r,
b
)
)
)
),
MAP(
A2:A7,
LAMBDA(
x,
r(
r,
x
)
)
)
)Excel solution 2 for Divide by 7 Recursively, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A7,
LAMBDA(
a,
LET(
r,
LAMBDA(
r,
a,
[t],
IF(
a=1,
t,
LET(
b,
ROUNDUP(
a/7,
),
r(
r,
b,
t+b
)
)
)
),
r(
r,
a
)
)
)
)Excel solution 3 for Divide by 7 Recursively, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A7,
LAMBDA(
x,
SUM(
SCAN(
x,
SEQUENCE(
ROUNDUP(
LOG(
x,
7
),
)
),
LAMBDA(
a,
n,
ROUNDUP(
a/7,
)
)
)
)
)
)Excel solution 4 for Divide by 7 Recursively, proposed by Rick Rothstein:
=MAP(
A2:A7,
LAMBDA(
z,
SUM(
SCAN(
z,
ROW(
1:12
),
LAMBDA(
a,
x,
IF(
a>1,
ROUNDUP(
a/7,
0
),
0
)
)
)
)
)
)Excel solution 5 for Divide by 7 Recursively, proposed by John V.:
=MAP(A2:A7,
LAMBDA(x,
SUM(1,
TOCOL(SCAN(x,
ROW(
1:9
),
LAMBDA(i,
x,
1+1/(ROUNDUP(
i/7,
)-1)^-1)),
2))))
✅ =MAP(
A2:A7,
LAMBDA(
x,
SUM(
1,
SCAN(
x,
SEQUENCE(
LOG(
x,
7
)
),
LAMBDA(
i,
x,
ROUNDUP(
i/7,
)
)
)
)
)
)Excel solution 6 for Divide by 7 Recursively, proposed by محمد حلمي:
=MAP(
A2:A7,
LAMBDA(
a,
LET(
r,
LAMBDA(
ME,
e,
LET(
v,
ROUNDUP(
e/7,
),
IF(
v=1,
0,
v+ME(
ME,
v
)
)
)
),
r(
r,
a
)
)+1
)
)Excel solution 7 for Divide by 7 Recursively, proposed by محمد حلمي:
=MAP(
A2:A7,
LAMBDA(
a,
SUM(
UNIQUE(
REDUCE(
a,
SEQUENCE(
9
),
LAMBDA(
a,
d,
VSTACK(
a,
ROUNDUP(
a/7,
)
)
)
)
)
)-a
)
)Excel solution 8 for Divide by 7 Recursively, proposed by 🇰🇷 Taeyong Shin:
=MAP(
A2:A7,
LAMBDA(
m,
LET(
Loop,
LAMBDA(
ME,
n,
LET(
n,
ROUNDUP(
n / 7,
0
),
IF(
n = 1,
n,
n + ME(
ME,
n
)
)
)
),
Loop(
Loop,
m
)
)
)
)Excel solution 9 for Divide by 7 Recursively, proposed by Kris Jaganah:
=LET(a,
A2:A7,
MAP(a,
LAMBDA(m,
LET(p,
SCAN(
m,
SEQUENCE(
9
),
LAMBDA(
x,
y,
ROUNDUP(
x/7,
0
)
)
),
SUM(
p
)-SUM(--(p=1))+1))))Excel solution 10 for Divide by 7 Recursively, proposed by Julian Poeltl:
=MAP(
A2:A7,
LAMBDA(
N,
SUM(
LET(
R,
ROUNDUP(
N/7^SEQUENCE(
10
),
0
),
TAKE(
R,
XMATCH(
1,
R
)
)
)
)
)
)Excel solution 11 for Divide by 7 Recursively, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_d,
A2:A7,
_f,
LAMBDA(
ME,
a,
b,
IF(
a = 1,
b,
ME(
ME,
ROUNDUP(
a / 7,
0
),
b + ROUNDUP(
a / 7,
0
)
)
)
),
_r,
MAP(
_d,
LAMBDA(
x,
_f(
_f,
x,
0
)
)
),
_r
)Excel solution 12 for Divide by 7 Recursively, proposed by Timothée BLIOT:
=LET(
A,
A2:A7,
F,
LAMBDA(
Self,
in,
out,
IF(
in=1,
out,
Self(
Self,
ROUNDUP(
in/7,
0
),
out+ROUNDUP(
in/7,
0
)
)
)
),
MAP(
A,
LAMBDA(
x,
F(
F,
x,
0
)
)
)
)Excel solution 13 for Divide by 7 Recursively, proposed by Hussein SATOUR:
=MAP(
A2:A7,
LAMBDA(
x,
LET(
a,
ROUNDUP(
LN(
x
) / LN(
7
),
0
),
SUM(
ROUNDUP(
x / POWER(
7,
SEQUENCE(
a,
,
a,
-1
)
),
0
)
)
)
)
)Excel solution 14 for Divide by 7 Recursively, proposed by Charles Roldan:
=MAP(A2:A7,
LAMBDA(x,
SUM(1 + INT((x - 1)/7^SEQUENCE(
1 + IFERROR(
LOG(
x - 1,
7
),
)
)))))Excel solution 15 for Divide by 7 Recursively, proposed by Charles Roldan:
=LET(f,
LAMBDA(x,
1 + QUOTIENT((x-1),
7)),
g,
LAMBDA(
u,
x,
LET(
y,
f(
x
),
y + IF(
y > 1,
u(
u,
y
)
)
)
),
MAP(
A2:A7,
LAMBDA(
x,
g(
g,
x
)
)
))Excel solution 16 for Divide by 7 Recursively, proposed by Victor Momoh (MVP, MOS, R.Eng):
=MAP(
A2:A7,
LAMBDA(
x,
LET(
R,
LAMBDA(
ME,
num,
LET(
a,
ROUNDUP(
num/7,
0
),
IF(
a>1,
a+ME(
ME,
a
),
a
)
)
),
R(
R,
x
)
)
)
)Excel solution 17 for Divide by 7 Recursively, proposed by Abhishek Kumar Jain:
=MAP(
A2:A7,
LAMBDA(
a,
SUM(
SCAN(
a,
SEQUENCE(
ROUNDUP(
LOG(
a,
7
),
0
& )
),
LAMBDA(
x,
y,
ROUNDUP(
x/7,
0
)
)
)
)
)
)Excel solution 18 for Divide by 7 Recursively, proposed by Guillermo Arroyo:
= SUM(
SCAN(
A2,
SEQUENCE(
ROUNDUP(
LOG(
A2,
7
),
0
),
,
7,
0
),
LAMBDA(
a,
b,
ROUNDUP(
a / b,
0
)
)
)
)Excel solution 19 for Divide by 7 Recursively, proposed by Viswanathan M B:
=Lambda(
a,
b,
LET(
result,
ROUNDUP(
a / 7,
0
),
IF(
result = 1,
b + 1,
fn(
result,
b + result
)
)
)
)
and then
=Map(
a2:a7,
lambda(
a,
fn(
a,
0
)
)
)Solving the challenge of Divide by 7 Recursively with Python
Python solution 1 for Divide by 7 Recursively, proposed by Igor Perković:
nums = {38,124,500,999,3456,99999}
for _,v in pairs(nums) do
x = (-math.floor(-v//7))
res = x
while x ~= 1 do
x = (-math.floor(-x//7))
res = res + x
end
print(v,res)
end
Solving the challenge of Divide by 7 Recursively with Python in Excel
Python in Excel solution 1 for Divide by 7 Recursively, proposed by Alejandro Campos:
import time
numbers = xl("A2:A7")[0]
def roundup_division_sum(num):
total_sum = 0
while num > 1:
num = (num + 6) // 7
total_sum += num
return total_sum
start_time = time.time()
df_results = pd.DataFrame({
'Number': numbers,
'Sum': [roundup_division_sum(num) for num in numbers]
})
end_time = time.time()
execution_time = (end_time - start_time)*1000
df_time = pd.DataFrame({
'Number': ['Execution Time'],
'Sum': [f"{execution_time:.2f} milisecs"]
})
df_final = pd.concat([df_results, df_time], ignore_index=True)
df_final
Solving the challenge of Divide by 7 Recursively with SQL
SQL solution 1 for Divide by 7 Recursively, proposed by Zoran Milokanović:
WITH /* Microsoft SQL Server 2019 */
SOLUTION
AS
(
SELECT
D.NUMBER, CAST(CEILING(D.NUMBER * 1.0 /7) AS INTEGER) AS ROUNDUP_NUMBER
FROM DATA D
UNION ALL
SELECT
D.NUMBER, CAST(CEILING(D.ROUNDUP_NUMBER * 1.0 /7) AS INTEGER) AS ROUNDUP_NUMBER
WHERE
CEILING(D.ROUNDUP_NUMBER * 1.0 /7) <> D.ROUNDUP_NUMBER
)
SELECT
S.NUMBER
,SUM(S.ROUNDUP_NUMBER) AS SUM
GROUP BY
S.NUMBER
ORDER BY
1
OPTION (MAXRECURSION 32767)
;
&&