Fill in the blank with max of (max of remaining cells of row going to right and max of remaining cells of column going down). Hence, if we see cell C2, max of remaining cells in this row going to right (D2:J2) is 81 and max of remaining cells in this column going down (C3:C11) is 95. Max of these 2 values is 95.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 687
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Fill Cell With Max Right-Down with Power Query
Power Query solution 1 for Fill Cell With Max Right-Down, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.ToRows(A),
C = List.Positions(B),
D = List.TransformMany(
C,
each C,
(v, w) =>
if B{w}{v} = null then
List.Max(List.Skip(List.Zip(B){v}, w) & List.Skip(B{w}, v))
else
B{w}{v}
),
E = Table.FromColumns(List.Split(D, List.Max(C) + 1))
in
EPower Query solution 2 for Fill Cell With Max Right-Down, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = List.Transform,
B = List.Zip,
C = Table.FromRows,
D = List.RemoveFirstN,
E = List.Max,
List = Table.ToRows(Source),
Nulls = A(List, each List.PositionOf(_, null, 2)),
Tbl = C(B({{0 .. Table.RowCount(Source) - 1}, Nulls})),
Exp = Table.ExpandListColumn(Tbl, "Column2"),
NoNull = Table.ToRows(Table.SelectRows(Exp, each ([Column2] <> null))),
Rows = A(NoNull, each E(D(Table.ToRows(Source){_{0}}, _{1} + 1))),
Cols = A(NoNull, each E(D(Table.ToColumns(Source){_{1}}, _{0}))),
Max = A(B({Cols, Rows}), E),
List2 = B({List.Positions(List.Combine(List)), List.Combine(List)}),
Zip = B({A(List.Select(List2, each _{1} = null), each _{0}), Max}),
Sol = C(
List.Split(
A(List.Sort(Zip & List.Select(List2, each _{1} <> null), each _{0}), each _{1}),
Table.ColumnCount(Source)
)
)
in
SolPower Query solution 3 for Fill Cell With Max Right-Down, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Cols = Table.ToColumns(Source),
Rows = Table.ToRows(Source),
Lst = List.TransformMany(List.Positions(Cols), each List.Positions(Rows), Fun),
Fun = (x, y) =>
Cols{x}{y} ?? List.Max(List.RemoveFirstN(Cols{x}, y) & List.RemoveFirstN(Rows{y}, x)),
Res = Table.FromColumns(List.Split(Lst, List.Count(Rows)))
in
ResPower Query solution 4 for Fill Cell With Max Right-Down, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ColNames = Table.ColumnNames(Source),
Cols = Table.ToColumns(Source),
AddIdx = Table.AddIndexColumn(Source, "Idx", 1),
Res = Table.RemoveColumns(List.Accumulate(List.Positions(ColNames), AddIdx, Fun_1), "Idx"),
Fun_1 = (a, v) =>
Table.ReplaceValue(a, null, each Fun_2(_, v), Replacer.ReplaceValue, {ColNames{v}}),
Fun_2 = (x, y) =>
[
A = List.Range(Record.ToList(x), y + 1),
B = List.Range(Cols{y}, x[Idx]),
C = List.Max(List.Combine({A, B}))
][C]
in
ResPower Query solution 5 for Fill Cell With Max Right-Down, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = [
Lst = Table.ToRows(Source),
C = List.Count,
P = List.Positions,
L = List.Transform,
R = List.Range,
fin = Table.FromRows(
L(
P(Lst),
(f) =>
L(
P(Lst{f}),
(x) =>
if Lst{f}{x} is null then
List.Max(R(Lst{f}, x, C(Lst{f})) & R(Table.ToColumns(Source){x}, f, C(Lst{f})))
else
Lst{f}{x}
)
)
)
][fin]
in
ResultPower Query solution 6 for Fill Cell With Max Right-Down, proposed by Peter Krkos:
PowerQuery solution:
= Table.FromRows(
List.Transform(Table.ToRows(Source), each
List.Accumulate(List.PositionOf(_, null, Occurrence.All), _, (s,c)=>
List.ReplaceRange(s, c, 1, {List.Max(Table.ToColumns(Source){c} & List.Skip(s, c))})))
)
Solving the challenge of Fill Cell With Max Right-Down with Excel
Excel solution 1 for Fill Cell With Max Right-Down, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:J11,LAMBDA(x,IF(x,x,MAX(TAKE(x:J11,1),TAKE(x:J11,,1)))))Excel solution 2 for Fill Cell With Max Right-Down, proposed by Rick Rothstein:
=MAP(
A2:J11,
LAMBDA(
x,
IF(
x,
x,
MAX(
OFFSET(
x,
,
,
1,
12-COLUMN(
x
)
),
OFFSET(
x,
,
,
12-ROW(
x
),
1
)
)
)
)
)Excel solution 3 for Fill Cell With Max Right-Down, proposed by John V.:
=MAP(A2:J11,LAMBDA(x,IF(x,x,MAX(TAKE(x:J11,1),TAKE(x:J11,,1)))))Excel solution 4 for Fill Cell With Max Right-Down, proposed by Kris Jaganah:
=LET(
a,
A2,
b,
J11,
IF(
a:b,
a:b,
MAP(
a:b,
LAMBDA(
x,
MAX(
TAKE(
b:x,
1,
),
TAKE(
b:x,
,
1
)
)
)
)
)
)Excel solution 5 for Fill Cell With Max Right-Down, proposed by Timothée BLIOT:
=LET(A,LAMBDA(n,m,INDEX(A2:J11,n,m)),B,LAMBDA(u,v,w,IFERROR(DROP(u,v,w),0)),MAKEARRAY(10,10,LAMBDA(x,y,IF(A(x,y),A(x,y),MAX(B(A(x,),,y),B(A(,y),x,))))))Excel solution 6 for Fill Cell With Max Right-Down, proposed by Hussein SATOUR:
=MAP(
A2:J11,
LAMBDA(
x,
IF(
x="",
MAX(
OFFSET(
x,
,
,
12-ROW(
x
)
),
OFFSET(
x,
,
,
,
11-COLUMN(
x
)
)
),
x
)
)
)Excel solution 7 for Fill Cell With Max Right-Down, proposed by Oscar Mendez Roca Farell:
=MAP(
A2:J11,
LAMBDA(
i,
IF(
i,
i,
MAX(
INDEX(
i:J11,
,
1
),
INDEX(
i:J11,
1,
)
)
)
)
)Excel solution 8 for Fill Cell With Max Right-Down, proposed by Duy Tùng:
=MAP(A2:J11,LAMBDA(x,IF(x,x,MAX(IFERROR(OFFSET(x,1,,11-ROW(x)),0),OFFSET(x,,1,,9-COLUMN(x))))))Excel solution 9 for Fill Cell With Max Right-Down, proposed by Sunny Baggu:
=IF(
A2:J11,
A2:J11,
MAP(
A2:J11,
LAMBDA(
x,
MAX(
INDEX(
x:J11,
1,
),
INDEX(
x:J11,
,
1
)
)
)
)
)Excel solution 10 for Fill Cell With Max Right-Down, proposed by Sunny Baggu:
=LET(
_a, MAP(
A2:J11,
LAMBDA(a,
MAX(OFFSET(a, 0, 0, 1, COLUMNS(TAKE(a:J11, 1))))
)
),
_b, MAP(
A2:J11,
LAMBDA(a,
MAX(OFFSET(a, 0, 0, ROWS(TAKE(a:J11, , 1)), 1))
)
),
_c, MAP(_a, _b, LAMBDA(x, y, MAX(x, y))),
IF(A2:J11, A2:J11, _c)
)Excel solution 11 for Fill Cell With Max Right-Down, proposed by Md. Zohurul Islam:
=LET(z,A2:J11,nr,ROWS(z),nc,COLUMNS(z),
u,MAKEARRAY(nr,nc,LAMBDA(x,y,LET(
a,INDEX(z,x,y),
j,MAX(IFERROR(DROP(INDEX(z,x,),,y),0)),
k,MAX(IFERROR(DROP(INDEX(z,,y),x),0)),
b,IF(a>0,a,MAX(j,k)),b))),
u)Excel solution 12 for Fill Cell With Max Right-Down, proposed by Pieter de B.:
=MAP(
A2:J11,
LAMBDA(
g,
IF(
g,
g,
MAX(
TAKE(
g:J11,
1
),
TAKE(
g:J11,
,
1
)
)
)
)
)Excel solution 13 for Fill Cell With Max Right-Down, proposed by Hamidi Hamid:
=LET(x,MAP(A2:J11,LAMBDA(a,MAX(TAKE(A11:J11:a,1,)))),y,MAP(A2:J11,LAMBDA(a,MAX(TAKE(J2:J11:a,,1)))),z,IF(A2:J11<>"",A2:J11,MAP(x,y,LAMBDA(a,b,MAX(a,b)))),z)Excel solution 14 for Fill Cell With Max Right-Down, proposed by Asheesh Pahwa:
=MAP(
A2:J11,
LAMBDA(
a,
LET(
c,
OFFSET(
a,
,
,
,
COLUMNS(
a:J11
)
),
r,
MAX(
OFFSET(
a,
,
,
ROWS(
a:J11
)
)
),
IF(
a,
a,
MAX(
c,
r
)
)
)
)
)Excel solution 15 for Fill Cell With Max Right-Down, proposed by Asheesh Pahwa:
=LET(d,A2:J11,m,MAP(A2:J11,LAMBDA(x,MAX(INDEX(x:J11,1,),INDEX(x:J11,,1)))),IF(d,d,m))Excel solution 16 for Fill Cell With Max Right-Down, proposed by ferhat CK:
=LET(a,A2:J11,i,LAMBDA(x,y,INDEX(a,x,y)),MAKEARRAY(10,10,LAMBDA(x,y,IF(i(x,y)="",MAX(IFERROR(i(x,SEQUENCE(10-y,,y+1)),0),IFERROR(i(SEQUENCE(10-x,,x+1),y),0)),i(x,y)))))Excel solution 17 for Fill Cell With Max Right-Down, proposed by Ankur Sharma:
=IF(A2:J11 = "",
MAX(INDEX(A2:J11, ROW(A2:J11) - 1, COLUMN(A2:J11)):INDEX(A2:J11, ROW(A2:J11) - 1, 10),
INDEX(A2:J11, ROW(A2:J11) - 1, COLUMN(A2:J11)):INDEX(A2:J11, 10, COLUMN(A2:J11))),
A2:J11)Excel solution 18 for Fill Cell With Max Right-Down, proposed by Meganathan Elumalai:
=MAP(A2:J11,LAMBDA(x,IF(x,x,LET(a,x:J11,MAX(TAKE(a,1),TAKE(a,,1))))))Excel solution 19 for Fill Cell With Max Right-Down, proposed by Ziad A.:
=MAP(
A2:J11,
LAMBDA(
_,
IF(
_,
_,
MAX(
INDEX(
_:J11,
1,
),
INDEX(
_:J11,
,
1
)
)
)
)
)Excel solution 20 for Fill Cell With Max Right-Down, proposed by Maciej Kopczyński:
=LET(
arr, A2:J11,
lastCell, J11,
result, MAP(arr, LAMBDA(cell, IF(cell, cell, MAX(INDEX(cell:lastCell, , 1), INDEX(cell:lastCell, 1, ))))),
result
)Excel solution 21 for Fill Cell With Max Right-Down, proposed by Fredson Alves Pinho:
=MAP(
A2:J11,
LAMBDA(
v,
IF(
v,
v,
MAX(
J11:v J2:v,
J11:v A11:v
)
)
)
)Excel solution 22 for Fill Cell With Max Right-Down, proposed by Craig Runciman:
=LET(
grid,
--A2:J11,
seq,
SEQUENCE(
10,
10
),
_z,
FILTER(
TOCOL(
seq
),
TOCOL(
grid
)=0
),
REDUCE(
grid,
_z,
LAMBDA(
acc,
v,
LET(
fr,
XMATCH(
0,
TOCOL(
acc
),
0
),
r,
CEILING(
v,
10
)/10,
c,
IF(
VALUE(
RIGHT(
v,
1
)
),
VALUE(
& RIGHT(
v,
1
)
),
10
),
rr,
IFERROR(
MAX(
DROP(
INDEX(
grid,
r,
),
,
c
)
),
0
),
cc,
IFERROR(
MAX(
DROP(
INDEX(
grid,
,
c
),
r
)
),
0
),
IF(
seq=fr,
MAX(
rr,
cc
),
acc
)
)
)
)
)Excel solution 23 for Fill Cell With Max Right-Down, proposed by CA Mohit Saxena:
=LET(l,J11,MAP(A2:l,LAMBDA(x,IF(x<>"",x,MAX(MAX(OFFSET(x,,,1,COLUMNS(TAKE(x:l,1))),MAX(OFFSET(x,,,ROWS(TAKE(x:l,,1))))))))))Excel solution 24 for Fill Cell With Max Right-Down, proposed by Dilip Pandey, MVP:
=MAX(IFERROR(MAX(OFFSET(D2,0,0,1,COUNTA(D2:$K2))),0),IFERROR(MAX(OFFSET(C3,0,0,COUNTA(C3:C$12),1)),0))Solving the challenge of Fill Cell With Max Right-Down with Python
Python solution 1 for Fill Cell With Max Right-Down, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "687 Fill Grid with Max Right and Down.xlsx"
input_matrix = pd.read_excel(path, usecols="A:J", skiprows=1, nrows=10, header=None).to_numpy()
test_matrix = pd.read_excel(path, usecols="A:J", skiprows=13, nrows=10, header=None).to_numpy()
def fill_empty(matrix, row, col):
return np.nanmax(np.r_[matrix[row, col:], matrix[row:, col]])
empty_cells = np.argwhere(np.isnan(input_matrix))
filled_values = [fill_empty(input_matrix, row, col) for row, col in empty_cells]
result = np.allclose(filled_values, test_matrix[tuple(empty_cells.T)], equal_nan=True)
print(result)
Solving the challenge of Fill Cell With Max Right-Down with Python in Excel
Python in Excel solution 1 for Fill Cell With Max Right-Down, proposed by Alejandro Campos:
grid = xl("A2:J11").fillna('')
rows, cols = len(grid), len(grid[0])
for i in range(rows):
for j in range(cols):
if grid[i][j] == '':
grid[i][j] = max(
max((x if x != '' else float('-inf')) for x in grid[i][j+1:]) if j+1 < cols else float('-inf'),
max((grid[k][j] if grid[k][j] != '' else float('-inf')) for k in range(i+1, rows)) if i+1 < rows else float('-inf'))
df = pd.DataFrame(grid)
Solving the challenge of Fill Cell With Max Right-Down with R
R solution 1 for Fill Cell With Max Right-Down, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/687 Fill Grid with Max Right and Down.xlsx"
input = read_excel(path, range = "A2:J11", col_names = FALSE) %>% as.matrix()
test = read_excel(path, range = "A14:J23", col_names = FALSE) %>% as.matrix()
fill_empty = function(matrix, row, col){
max = max(c(matrix[row, col:ncol(matrix) ], matrix[row:nrow(matrix), col]), na.rm = TRUE)
return(max)
}
empty_cells = which(is.na(input), arr.ind = TRUE)
test_values = test[empty_cells]
filled_values = as.data.frame(empty_cells) %>%
mutate(value = map2_dbl(row, col, ~ fill_empty(input, .x, .y))) %>%
select(value)
all.equal(filled_values$value, test_values)
# [1] TRUE
&&