Today’s challenge is proposed by Sunny Baggu a is for row 1 and b is for column 1. Find the value corresponding to a and b where a and b may not be exact values. In that case, you will need to find interpolated value.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 440
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Interpolate Grid Position Values with Power Query
Power Query solution 1 for Interpolate Grid Position Values, proposed by Aditya Kumar Darak 🇮🇳:
let
Data = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Lookup = Excel.CurrentWorkbook(){[Name = "lookup"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Lookup, {"a/b"}, "L2", "Value"),
Type = Table.TransformColumnTypes(Unpivot, {"L2", type number}),
Return = Table.AddColumn(
Data,
"Answer",
each [
A1 = Number.RoundDown([a], 1),
A2 = Number.RoundUp([a], 1),
D1 = A2 - A1,
B1 = Number.RoundDown([b], 0),
B2 = Number.RoundUp([b], 0),
D2 = B2 - B1,
V1 = Type{[#"a/b" = B1, L2 = A1]}[Value],
V2 = Type{[#"a/b" = B2, L2 = A1]}[Value],
V3 = Type{[#"a/b" = B1, L2 = A2]}[Value],
X1 = if D2 = 0 then 0 else (V2 - V1) * ([b] - B1) / D2,
X2 = if D1 = 0 then 0 else (V3 - V1) * ([a] - A1) / D1,
R = Number.Round(V1 + X1 + X2, 3)
][R]
)
in
ReturnPower Query solution 2 for Interpolate Grid Position Values, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Tabla1 = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Filas = Table.ToRows(Tabla1),
Tabla2 = Excel.CurrentWorkbook(){[Name = "Tabla2"]}[Content],
Sol = Table.AddColumn(
Tabla1,
"Answer",
(x) =>
let
a = List.PositionOf(Table.ColumnNames(Tabla2), Text.From(Record.ToList(x){0})),
b = List.PositionOf(List.First(Table.ToColumns(Tabla2)), Record.ToList(x){1}),
c =
if a > 0 and b > 0 then
Table.ToColumns(Tabla2){a}{b}
else
let
A = Table.ColumnNames(Tabla2),
B = List.PositionOf(
A,
List.Last(List.Select(List.Skip(A), each Number.From(_) < x[a]))
),
C = List.First(Table.ToColumns(Tabla2)),
D = List.PositionOf(C, List.Last(List.Select(C, each _ < x[b]))),
E = List.Transform(
List.Transform({D, D + 1}, each Table.ToRows(Tabla2){_}),
(x) => List.Transform({B, B + 1}, (y) => x{y})
),
F = List.Transform({B, B + 1}, each Number.From(Table.ColumnNames(Tabla2){_})),
G = List.Transform({D, D + 1}, each C{_}),
H = (F{1} - x[a]) / (F{1} - F{0}) * E{0}{0} + (x[a] - F{0}) / (F{1} - F{0}) * E{0}{1},
I = (F{1} - x[a]) / (F{1} - F{0}) * E{1}{0} + (x[a] - F{0}) / (F{1} - F{0}) * E{1}{1},
J = (G{1} - x[b]) / (G{1} - G{0}) * H + (x[b] - G{0}) / (G{1} - G{0}) * I
in
J
in
c
)[[Answer]]
in
SolSolving the challenge of Interpolate Grid Position Values with Excel
Excel solution 1 for Interpolate Grid Position Values, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A5,B2:B5,LAMBDA(a,b,LET(r,E2:E7,c,--F1:M1,
i,XMATCH(b,r,{-1;1}),j,XMATCH(a,c,{-1,1}),
TREND(BYROW(INDEX(F2:M7,i,j),LAMBDA(x,TREND(x,INDEX(c,j),a))),INDEX(r,i),b))))Excel solution 2 for Interpolate Grid Position Values, proposed by John V.:
=MAP(
A2:A5,
B2:B5,
LAMBDA(
a,
b,
TREND(
BYROW(
INDEX(
F2:M7,
b+{0;1},
10*a+{0,
1}
),
LAMBDA(
r,
TREND(
r,
TRUNC(
a,
1
)+{0,
0.1},
a
)
)
),
INT(
b
)+{0;1},
b
)
)
)Excel solution 3 for Interpolate Grid Position Values, proposed by محمد حلمي:
=MAP(A2:A5,
B2:B5,
LAMBDA(a,
b,
LET(i,
+OFFSET(
E1,
XMATCH(
b,
E2:E7,
-1
),
XMATCH(
-a,
-F1:M1,
1
),
2,
2
),
ROUND(@i+AVERAGE((DROP(
i,
1
)-TAKE(
i,
1
))*MOD(
b,
1
))-(@i-TAKE(
i,
1,
-1
))*MOD(
a*10,
1
),
3))))Excel solution 4 for Interpolate Grid Position Values, proposed by Kris Jaganah:
=MAP(
A2:A5,
B2:B5,
LAMBDA(
x,
y,
LET(
a,
E2:E7,
b,
--F1:M1,
c,
F2:M7,
d,
XLOOKUP(
x,
b,
XLOOKUP(
y,
a,
c,
,
-1
),
,
{-1,
1}
),
e,
XLOOKUP(
x,
--b,
XLOOKUP(
y,
a,
c,
,
1
),
,
-1
),
ROUND(
@d+SUM(
MMULT(
d,
{-1;1}
)*MOD(
x*10,
1
),
MMULT(
HSTACK(
@d,
@e
),
{-1;1}
)*MOD(
y,
1
)
),
3
)
)
)
)Excel solution 5 for Interpolate Grid Position Values, proposed by Julian Poeltl:
=MAP(
A2:A5,
B2:B5,
LAMBDA(
aS,
bs,
LET(
xA,
F1:M1,
xB,
E2:E7,
T,
F2:M7,
xAl,
XMATCH(
aS,
xA,
1
),
xAs,
XMATCH(
aS,
xA,
-1
),
xBl,
XMATCH(
bs,
xB,
1
),
xBs,
XMATCH(
bs,
xB,
-1
),
b,
VSTACK(
xBs,
xBl
),
a,
HSTACK(
xAs,
xAl
),
TREND(
BYROW(
INDEX(
T,
b,
a
),
LAMBDA(
x,
TREND(
x,
INDEX(
xA,
a
),
aS
)
)
),
INDEX(
xB,
b
),
bs
)
)
)
)Excel solution 6 for Interpolate Grid Position Values, proposed by Timothée BLIOT:
=MAP(A2:A5,
B2:B5,
LAMBDA(x,
y,
LET(A,
FLOOR(
x,
0.1
),
B,
FLOOR(
y,
1
),
C,
(x-A)*10,
D,
y-B,
SUM(
OFFSET(
INDEX(
F2:M7,
B,
A*10
),
,
,
2,
2
)*HSTACK(
1-D,
D
)*VSTACK(
1-C,
C
)
))))Excel solution 7 for Interpolate Grid Position Values, proposed by Sunny Baggu:
=MAP(
A2:A5,
B2:B5,
LAMBDA(
a,
b,
LET(
c,
--F1:M1,
d,
E2:E7,
rng,
F2:M7,
_v,
XMATCH(
b,
d,
{-1; 1}
),
_h,
XMATCH(
a,
c,
{-1,
1}
),
arr,
INDEX(
rng,
_v,
_h
),
_r,
FORECAST.LINEAR(
a,
TOCOL(
HSTACK(
FORECAST.LINEAR(
b,
TAKE(
arr,
,
1
),
INDEX(
d,
_v
)
),
FORECAST.LINEAR(
b,
TAKE(
arr,
,
-1
),
INDEX(
d,
_v
)
)
)
),
TOCOL(
INDEX(
c,
,
_h
)
)
),
IFERROR(
_r,
XLOOKUP(
b,
d,
XLOOKUP(
a,
c,
rng
)
)
)
)
)
)Excel solution 8 for Interpolate Grid Position Values, proposed by LEONARD OCHEA 🇷🇴:
=LET(
h,
F1:M1,
v,
E2:E7,
d,
F2:M7,
s,
{-1;1},
B,
LAMBDA(
a,
b,
c,
XLOOKUP(
a,
b,
b,
,
c
)
),
C,
LAMBDA(
a,
b,
XMATCH(
a,
b
)
),
MAP(
A2:A5,
B2:B5,
LAMBDA(
k,
l,
LET(
x,
B(
k,
h,
s
),
TREND(
MAP(
s,
LAMBDA(
y,
TREND(
INDEX(
d,
C(
B(
l,
v,
y
),
v
),
C(
x,
h
)
),
x,
k
)
)
),
B(
l,
v,
s
),
l
)
)
)
)
)Excel solution 9 for Interpolate Grid Position Values, proposed by Andy Heybruch:
=MAP(A2:A5*10,
B2:B5,
LAMBDA(_a,
_b,
LET(
_rng,
F2:M7,
_mina,
INDEX(
_rng,
INT(
_b
),
INT(
_a
)
),
_mina+
MOD(
_a,
1
)*(INDEX(
_rng,
INT(
_b
),
INT(
_a
)+1
)-_mina)+
MOD(
_b,
1
)*(INDEX(
_rng,
INT(
_b
)+1,
INT(
_a
)
)-_mina))))Solving the challenge of Interpolate Grid Position Values with Python
Python solution 1 for Interpolate Grid Position Values, proposed by Konrad Gryczan, PhD:
Again inspired by Aditya Kumar Darak 🇮🇳
import pandas as pd
input = pd.read_excel("439 - Bilinear Interpolation.xlsx", usecols="A:B", nrows=4)
lookup_table = pd.read_excel("439 - Bilinear Interpolation.xlsx", usecols="E:M", nrows = 7)
test = pd.read_excel("439 - Bilinear Interpolation.xlsx", usecols="C:C", nrows = 4)
lookup_table = lookup_table.set_index("a/b")
def bilinear_interpolation(a, b, lookup_table):
a_low = int(a * 10) / 10
a_high = int(a * 10 + 1) / 10
b_low = int(b)
b_high = int(b + 1)
dist_a = a_high - a_low
dist_b = b_high - b_low
vlook_1 = lookup_table.loc[b_low, str(a_low)]
vlook_2 = lookup_table.loc[b_high, str(a_low)]
vlook_3 = lookup_table.loc[b_low, str(a_high)]
x_1 = 0 if dist_b == 0 else (vlook_2 - vlook_1) * (b - b_low) / dist_b
x_2 = 0 if dist_a == 0 else (vlook_3 - vlook_1) * (a - a_low) / dist_a
value = vlook_1 + x_1 + x_2
return round(value, 3)
result = input.copy()
result["Answer Expected"] = result.apply(lambda row: bilinear_interpolation(row["a"], row["b"], lookup_table), axis=1)
print(result["Answer Expected"].equals(test["Answer Expected"])) # True
Solving the challenge of Interpolate Grid Position Values with Python in Excel
Python in Excel solution 1 for Interpolate Grid Position Values, proposed by Alejandro Campos:
from scipy.interpolate import interp2d
table_df = xl("E1:M7", headers=True)
points_df = xl("A1:B5", headers=True)
A = table_df.columns[1:].astype(float).tolist()
B = table_df['B\A'].tolist()
values = table_df.iloc[:,1:].values
interp_func = interp2d(A, B, values)
interpolated_values = [interp_func(a,b)[0] for a,b in zip(points_df['a'], points_df['b'])]
points_df['Interpolated Value'] = interpolated_values
points_df
Solving the challenge of Interpolate Grid Position Values with R
R solution 1 for Interpolate Grid Position Values, proposed by Konrad Gryczan, PhD:
Aditya Kumar Darak 🇮🇳
library(tidyverse)
library(readxl)
input = read_excel("Excel/439 - Bilinear Interpolation.xlsx", range = "A1:B5")
lookup = read_excel("Excel/439 - Bilinear Interpolation.xlsx", range = "E1:M7")
test = read_excel("Excel/439 - Bilinear Interpolation.xlsx", range = "C1:C5")
lookup = lookup %>%
column_to_rownames("a/b")
bilinear_interpolation = function(a, b, lookup_table) {
a_low = floor(a * 10) / 10
a_high = ceiling(a * 10) / 10
b_low = floor(b)
b_high = ceiling(b)
dist_a = a_high - a_low
dist_b = b_high - b_low
vlook_1 = lookup_table[as.character(b_low), as.character(a_low)]
vlook_2 = lookup_table[as.character(b_high), as.character(a_low)]
vlook_3 = lookup_table[as.character(b_low), as.character(a_high)]
x_1 = if_else(dist_b == 0, 0, (vlook_2 - vlook_1) * (b - b_low) / dist_b)
x_2 = if_else(dist_a == 0, 0, (vlook_3 - vlook_1) * (a - a_low) / dist_a)
value = vlook_1 + x_1 + x_2
return(round(value, 3))
}
result = input %>%
mutate(`Answer Expected` = map2_dbl(a, b, ~bilinear_interpolation(.x, .y, lookup)))
&&