Work out the largest sum for contiguous cells given in the range A2:A10. For example data, answer is 9 for A4:A9.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 320
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find largest sum of contiguous cells with Power Query
Power Query solution 1 for Find largest sum of contiguous cells, proposed by John V.:
let
d = Excel.CurrentWorkbook(){0}[Content][Numbers],
s =
List.Generate(
() => [i = 0, j = 1],
each [i] < List.Count(d),
each if [j] = List.Count(d) then [i = [i] + 1, j = [i] + 2] else [i = [i], j = [j] + 1],
each List.Range(d, [i], [j] - [i])
),
m = List.Max(List.Transform(s, each List.Sum(_)))
in
m
Blessings!
Power Query solution 2 for Find largest sum of contiguous cells, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content][Numbers],
S = List.Accumulate(
Source,
0,
(s, c) =>
let
g = List.Max,
m = s{0} + c
in
if s = 0 then {g({c, 0}), c} else {g({m, 0}), g({m, s{1}})}
){1}
in
SPower Query solution 3 for Find largest sum of contiguous cells, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content][Numbers],
Totals = List.TransformMany(
{1 .. List.Count(Source)},
(x) => {x .. List.Count(Source)},
(x, y) => List.Sum(List.Range(Source, x - 1, y - x + 1))
),
Return = List.Max(Totals)
in
ReturnPower Query solution 4 for Find largest sum of contiguous cells, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][Numbers],
Sol = List.Max(List.Combine(List.Transform({0..List.Count(Source)-1}, each
List.Transform({0..List.Count(List.Skip(Source,_))-1}, (y)=>
List.Sum(List.RemoveLastN(List.Skip(Source,_), y))))))
in
Sol
O, más detallado....
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][Numbers],
Comb = List.Transform({0..List.Count(Source)-1}, each
List.Transform({0..List.Count(List.Skip(Source,_))-1}, (y)=>
List.Sum(List.RemoveLastN(List.Skip(Source,_), y)))),
Sol = List.Max(List.Combine(Comb))
in
Sol
Power Query solution 5 for Find largest sum of contiguous cells, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = List.Max(
List.Transform(
{0 .. Table.RowCount(Source) - 1},
each
let
a = ({Source[Numbers]{_}} & {List.RemoveFirstN(Source[Numbers], _ + 1)}),
b = List.Max(
List.Transform(
{0 .. List.Count(a{1}) - 1},
each a{0} + List.Sum(List.RemoveLastN(a{1}, _))
)
)
in
b
)
)
in
SolPower Query solution 6 for Find largest sum of contiguous cells, proposed by Alexis Olson:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Nums = Table.TransformColumnTypes(Source, {{"Numbers", Int64.Type}})[Numbers],
Loop = List.Generate(
() => [best = List.Min(Nums), curr = 0, i = 0],
each [i] < List.Count(Nums),
each [
curr = List.Max({Nums{[i]}, [curr] + Nums{[i]}}),
best = List.Max({[best], curr}),
i = [i] + 1
],
each [best]
),
Answer = List.Max(Loop)
in
AnswerPower Query solution 7 for Find largest sum of contiguous cells, proposed by Alexis Olson:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Nums = Table.TransformColumnTypes(Source, {{"Numbers", Int64.Type}})[Numbers],
Answer = List.Accumulate(
Nums,
{List.First(Nums), 0},
(s, c) => {List.Max({s{0}, List.Max({c, s{1} + c})}), List.Max({c, s{1} + c})}
){0}
in
AnswerPower Query solution 8 for Find largest sum of contiguous cells, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
Num = List.Transform(Source[Numbers], each Number.From(_)),
Result = let
a = Table.FromColumns({{0..List.Count(Num)-1}}, {"Row1"}),
b = Table.AddColumn(a, "Index", each 1),
c = Table.NestedJoin(b,"Index",b, "Index","Join", 1)[[Row1],[Join]],
d = Table.ExpandTableColumn(c, "Join", {"Row1"}, {"Row2"}),
e = Table.AddColumn(d, "Check", each [Row2] > [Row1]),
f = Table.SelectRows(e, each [Check]),
g = Table.AddColumn(f, "Sum", each let
o = List.RemoveFirstN(Num, [Row1]),
p = List.FirstN(o, [Row2] + 1),
pp = List.FirstN(o, [Row2] - 1),
q = if [Row1] = 0 or [Row2] = 8 then p else pp,
r = List.Sum(q)
in
r
)[Sum]
in
Table.FromValue(List.Max(g))
in
Result
🧙♂️ 🧙♂️ 🧙♂️
Power Query solution 9 for Find largest sum of contiguous cells, proposed by Luke Jarych:
let
Source = Table1,
Numbers = Table.TransformColumnTypes(Source, {{"Numbers", Int64.Type}})[Numbers],
Numnber1 = Numbers{0},
Result = List.Generate(
() => [maxSum = 0, currentSum = 0, index = 0, NumbersIndex = 0, OnlyCurSum = 0],
each [index] < List.Count(Numbers),
each [
IndexNumber = [index],
NumbersIndex = Numbers{[index]} + [currentSum],
OnlyCurSum = [currentSum],
currentSum = List.Max({Numbers{[index]}, Numbers{[index]} + [currentSum]}),
maxSum = List.Max({[maxSum], currentSum}),
index = [index] + 1
],
each [maxSum]
)
in
List.Max(Result)Solving the challenge of Find largest sum of contiguous cells with Excel
Excel solution 1 for Find largest sum of contiguous cells, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
A2:A10,
MAX(
MAKEARRAY(
ROWS(
z
),
ROWS(
z
),
LAMBDA(
r,
c,
SUM(
TAKE(
DROP(
z,
r-1
),
c
)
)
)
)
)
)Excel solution 2 for Find largest sum of contiguous cells, proposed by Rick Rothstein:
=MAX(
MAP(
A2:A10,
LAMBDA(
n,
REDUCE(
0,
n:A10,
LAMBDA(
a,
x,
IF(
SUM(
n:x
)>a,
SUM(
n:x
),
a
)
)
)
)
)
)Excel solution 3 for Find largest sum of contiguous cells, proposed by John V.:
=MAX(
MAP(
A2:A10,
LAMBDA(
l,
MAX(
MAP(
A2:l,
LAMBDA(
f,
SUM(
f:l
)
)
)
)
)
)
)Excel solution 4 for Find largest sum of contiguous cells, proposed by محمد حلمي:
=MAX(
MAP(
A2:A10,
LAMBDA(
c,
MAX(
MAP(
A2:A10,
LAMBDA(
a,
MAX(
SUM(
a:c
)
)
)
)
)
)
)
)Excel solution 5 for Find largest sum of contiguous cells, proposed by محمد حلمي:
=MAX(
MAP(
A2:A10,
LAMBDA(
c,
MAX(
MAP(
A2:A10,
LAMBDA(
a,
MAX(
SUM(
a:c
)
)
)
)
)
)
)
)Excel solution 6 for Find largest sum of contiguous cells, proposed by Kris Jaganah:
=LET(
a,
A2:A10,
b,
ROWS(
a
),
c,
SEQUENCE(
b
),
MAX(
BYCOL(
XLOOKUP(
c+SEQUENCE(
,
b,
0
),
c,
a,
0
),
LAMBDA(
z,
MAX(
SCAN(
0,
z,
LAMBDA(
x,
y,
x+y
)
)
)
)
)
)
)Excel solution 7 for Find largest sum of contiguous cells, proposed by Julian Poeltl:
=MAX(
MAP(
SEQUENCE(
ROWS(
A2:A10
)
),
LAMBDA(
A,
MAX(
SCAN(
0,
DROP(
A2:A10,
A-1
),
LAMBDA(
A,
B,
A+B
)
)
)
)
)
)Excel solution 8 for Find largest sum of contiguous cells, proposed by Timothée BLIOT:
=MAX(
TOCOL(
MAKEARRAY(
9,
9,
LAMBDA(
x,
y,
SUM(
DROP(
DROP(
A2:A10,
x-1
),
-y+1
)
)
)
),
3
)
)Excel solution 9 for Find largest sum of contiguous cells, proposed by Hussein SATOUR:
=MAX(LET(a, MAP(A2:A10, LAMBDA(z, TEXTJOIN("|",, SCAN(,z:A10, LAMBDA(x,y, x&"/"&y))))), MAP(a, LAMBDA(u, MAX(BYROW(--TEXTSPLIT(u,"/", "|",1,,0), LAMBDA(w, SUM(w))))))))Excel solution 10 for Find largest sum of contiguous cells, proposed by Md. Zohurul Islam:
=LET(
z,
A2:A10,
n,
COUNT(
z
),
a,
SEQUENCE(
n
),
b,
SEQUENCE(
,
n
),
c,
TOCOL(
a+b-a
),
r,
TOCOL(
a+b-b
),
w,
MAP(
r,
c,
LAMBDA(
x,
y,
SUM(
TAKE(
DROP(
z,
x-1
),
y
)
)
)
),
MAX(
w
)
)Excel solution 11 for Find largest sum of contiguous cells, proposed by Md. Zohurul Islam:
LET(
z,
A2:A10,
a,
SCAN(
0,
z,
SUM
),
b,
TOROW(
a
),
MAX(
a-b
)
)Excel solution 12 for Find largest sum of contiguous cells, proposed by Charles Roldan:
=MAX(LAMBDA(
x,
x-TOROW(
x
)
)(SCAN(
,
A2:A10,
LAMBDA(
a,
b,
a+b
)
)))Excel solution 13 for Find largest sum of contiguous cells, proposed by JvdV -:
=MAX(
MAP(
A2:A10,
LAMBDA(
x,
MAX(
MAP(
x:A10,
LAMBDA(
y,
SUM(
x:y
)
)
)
)
)
)
)Excel solution 14 for Find largest sum of contiguous cells, proposed by Giorgi Goderdzishvili:
=LET(
arr,
A2:A10,
sq,
SEQUENCE(
ROWS(
arr
),
,
0
),
mp,
MAP(
sq,
LAMBDA(
x,
MAX(
SCAN(
0,
DROP(
arr,
x
),
LAMBDA(
a,
v,
a+v
)
)
)
)
),
MAX(
mp
)
)Excel solution 15 for Find largest sum of contiguous cells, proposed by Edwin Tisnado:
=MAX(LET(t,
A2:A10,
MAKEARRAY(ROWS(
t
),
ROWS(
t
),
LAMBDA(x,
y,
SUM(INDIRECT("A"&(x+1)&":A"&(y+1)))))))Excel solution 16 for Find largest sum of contiguous cells, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(
n,
A2:A10,
l,
ROWS(
n
),
f_max,
LAMBDA(
g,
LET(
s,
SEQUENCE(
l-g+1
),
REDUCE(
0,
s,
LAMBDA(
a,
i,
LET(
x,
SEQUENCE(
g,
,
i
& ),
MAX(
a,
SUM(
INDEX(
n,
x
)
)
)
)
)
)
)
),
REDUCE(
0,
SEQUENCE(
l
),
LAMBDA(
a,
gr,
MAX(
a,
f_max(
gr
)
)
)
)
)Excel solution 17 for Find largest sum of contiguous cells, proposed by Jeff Blakley:
=MAX(
MAP(
A2:A10,
LAMBDA(
x,
MAX(
SCAN(
0,
x:A10,
LAMBDA(
a,
b,
a+b
)
)
)
)
)
)Solving the challenge of Find largest sum of contiguous cells with Python
Solving the challenge of Find largest sum of contiguous cells with Python in Excel
Python in Excel solution 1 for Find largest sum of contiguous cells, proposed by John V.:
Hi everyone!
One [Python] Option could be:
max(n[i:j].sum() for i in l for j in l)
Blessings!
Solving the challenge of Find largest sum of contiguous cells with R
R solution 1 for Find largest sum of contiguous cells, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Largest Sum.xlsx", range = "A1:A10")
get_largest_cons_sum = function(numbers) {
max_len = length(numbers)
combs = expand.grid(start = 1:max_len, end = 1:max_len) %>%
filter(start < end)
sums = map2_dbl(combs$start, combs$end, ~ sum(numbers[.x:.y]))
res = max(sums)
return(res)
}
get_largest_cons_sum(input$Numbers)
Solving the challenge of Find largest sum of contiguous cells with Excel VBA
Excel VBA solution 1 for Find largest sum of contiguous cells, proposed by Nicolas Micot:
Function f_challenge320(Plage As Range) As Integer
Dim tableau As Variant
Dim posDep As Integer, posFin As Integer, pos As Integer, somme As Integer
Dim valMax
tableau = Plage.Value
For posDep = 1 To UBound(tableau, 1)
For posFin = posDep To UBound(tableau, 1)
somme = 0
For pos = posDep To posFin
somme = somme + tableau(pos, 1)
Next pos
If somme > valMax Or IsEmpty(valMax) Then valMax = somme
Next posFin
Next posDep
f_challenge320 = valMax
End Function
Solving the challenge of Find largest sum of contiguous cells with DAX
DAX solution 1 for Find largest sum of contiguous cells, proposed by Zoran Milokanović:
EVALUATEROW("Answer Expected",
MAXX(ADDCOLUMNS(GENERATEALL(Input, GENERATESERIES(1, Input[Index])), "LCS", VAR f = [Value]VAR t = Input[Index]
RETURNCALCULATE(SUM(Input[Numbers]), FILTER(ALL(Input), f <= Input[Index]&&Input[Index] <= t))), [LCS]))
&&