Today’s challenge is contributed by Mehmet Çiçek. Have you ever been facing of this issue when purchasing something: “What is the best choice at lowest cost?” There are 5 kind of bottles with different capacity (liter) and different cost. The higher capacity, the lower cost. If someone need to buy “n” liters, what the lowest cost should be?
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 628
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Calculate Cheapest Bottle Combination with Excel
Excel solution 1 for Calculate Cheapest Bottle Combination, proposed by Bo Rydobon 🇹🇭:
=MAP(
A9:A12,
LAMBDA(
a,
MAX(
REDUCE(
a*{1,
0},
SEQUENCE(
9
),
LAMBDA(
i,
_,
LET(
c,
B2:B6,
n,
INT(
@i/c
),
x,
XMATCH(
1,
n,
1
),
IF(
ISNA(
x
),
i,
HSTACK(
@i-INDEX(
n*c,
x
),
INDEX(
i,
2
)+INDEX(
n*C2:C6,
x
)
)
)
)
)
)
)
)
)Excel solution 2 for Calculate Cheapest Bottle Combination, proposed by Bo Rydobon 🇹🇭:
=MAP(A9:A12,LAMBDA(i,LET(c,TOROW(B2:B6),r,REDUCE(i&" 0",c,LAMBDA(a,i,LET(l,TEXTBEFORE(a," "),n,INT(l/c),UNIQUE(TOCOL(ROUND(l-n*c,2)&" "&n*TOROW(C2:C6)+TEXTAFTER(a," ")))))),
MIN(--FILTER(TEXTAFTER(r," "),TEXTBEFORE(r," ")="0")))))Excel solution 3 for Calculate Cheapest Bottle Combination, proposed by Bo Rydobon 🇹🇭:
=LET(
c,
B2:B6,
r,
LAMBDA(
r,
i,
j,
LET(
n,
INT(
i/c
),
x,
XMATCH(
1,
n,
1
),
IF(
ROUND(
@i,
9
),
r(
r,
i-INDEX(
n*c,
x
),
j+INDEX(
n*C2:C6,
x
)
),
j
)
)
),
MAP(
A9:A12,
LAMBDA(
a,
r(
r,
a,
)
)
)
)Excel solution 4 for Calculate Cheapest Bottle Combination, proposed by John V.:
=LET(F,LAMBDA(F,v,t,LET(n,(v>0)*MAX(B2,v),c,VLOOKUP(n,B2:C6,{1,2}),e,INT(n/@c),d,t+e*MAX(c),r,ROUND(n-@c*e,2),IF(r,F(F,r,d),d))),MAP(A9:A12,LAMBDA(x,F(F,x,))))Excel solution 5 for Calculate Cheapest Bottle Combination, proposed by 🇰🇷 Taeyong Shin:
=LET(
R,
LAMBDA(
R,
i,
j,
LET(
x,
XLOOKUP(
0.9,
i/B2:B6,
B2:C6,
0,
1
),
IF(
N(
x
),
R(
R,
i-N(
x
),
j+INDEX(
x,
2
)
),
j
)
)
),
MAP(
A9:A12,
LAMBDA(
x,
R(
R,
x,
)
)
)
)Excel solution 6 for Calculate Cheapest Bottle Combination, proposed by Kris Jaganah:
=LET(a,
B2:B6,
b,
C2:C6,
c,
A9:A12,
d,
XLOOKUP(
c,
a,
a,
,
-1
),
e,
c/d,
f,
INT(
e
),
g,
d*f,
h,
c-g,
i,
XLOOKUP(
h,
a,
a,
0,
-1
),
(IF(
h=0,
0,
h/i
)*XLOOKUP(
i,
a,
b,
0
))+(XLOOKUP(
d,
a,
b
)*f))Excel solution 7 for Calculate Cheapest Bottle Combination, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_bottleType,
A2:C6,
_input,
A9:A12,
_sort,
SORT(
_bottleType,
2,
-1
),
_rtrn,
MAP(
_input,
LAMBDA(
x,
LET(
rt,
SCAN(
x,
INDEX(
_sort,
,
2
),
LAMBDA(
a,
b,
MAX(
a - FLOOR.MATH(
a,
b
),
0
)
)
),
diff,
VSTACK(
x,
DROP(
rt,
-1
)
) - rt,
unts,
IF(
diff,
diff / INDEX(
_sort,
,
2
),
0
),
amt,
unts * TAKE(
_sort,
,
-1
),
rtrn,
SUM(
amt
),
rtrn
)
)
),
_rtrn
)Excel solution 8 for Calculate Cheapest Bottle Combination, proposed by Timothée BLIOT:
=MAP(A9:A12,LAMBDA(z,SUM(LET(B,B2:B6,C,C2:C6,XLOOKUP(REDUCE(0,ROW(1:9),LAMBDA(w,v,LET(A,CEILING(z-SUM(w),0.1),VSTACK(w,IF(A=0,0,XLOOKUP(A,B,B,,-1)))))),B,C,0)))))Excel solution 9 for Calculate Cheapest Bottle Combination, proposed by Hussein SATOUR:
=MAP(
A9:A12,
LAMBDA(
z,
LET(
S,
SORT,
R,
ROUNDDOWN,
L,
S(
B2:B6,
,
-1
),
C,
S(
C2:C6,
,
-1
),
a,
DROP(
SCAN(
,
VSTACK(
z,
L
),
LAMBDA(
x,
y,
x-R(
x/y,
0
)*y
)
),
-1
),
SUM(
C*R(
a/L,
0
)
)
)
)
)Excel solution 10 for Calculate Cheapest Bottle Combination, proposed by LEONARD OCHEA 🇷🇴:
=LET(
m,
B2:B6,
n,
C2:C6,
F,
LAMBDA(
F,
x,
a,
LET(
y,
XLOOKUP(
ROUND(
x,
1
),
m,
m,
,
-1
),
b,
a+XLOOKUP(
y,
m,
n
),
IF(
x-y>0,
F(
F,
x-y,
b
),
b
)
)
),
MAP(
A9:A12,
LAMBDA(
i,
F(
F,
i,
0
)
)
)
)Excel solution 11 for Calculate Cheapest Bottle Combination, proposed by Md. Zohurul Islam:
=LET(
a,
B2:B6,
b,
C2:C6,
z,
A9:A12,
_s1,
XMATCH(
z,
a,
-1
),
d,
INDEX(
b,
_s1
),
_s2,
INT(
z/XLOOKUP(
z,
a,
a,
,
-1
)
),
e,
d*_s2,
_s3,
MOD(
z,
INDEX(
a,
_s1
)
),
_s4,
XLOOKUP(
_s3,
a,
a,
0,
-1
),
_s5,
IFERROR(
_s3/_s4,
0
),
_s6,
XLOOKUP(
_s4,
a,
b,
0
),
f,
_s5*_s6,
result,
e+f,
result
)Excel solution 12 for Calculate Cheapest Bottle Combination, proposed by Pieter de B.:
=LET(
n,
INT(
TOROW(
A9:A12
)/B2:B6
),
c,
CHOOSECOLS,
s,
{1,
2,
3,
4,
5},
DROP(
REDUCE(
"",
A9:A12,
LAMBDA(
x,
m,
LET(
y,
REDUCE(
{0,
0},
s,
LAMBDA(
a,
b,
LET(
i,
c(
n,
b
),
j,
HSTACK(
TOCOL(
TOROW(
TAKE(
a,
,
1
)
)+i*c(
C2:C6,
b
)
),
TOCOL(
TOROW(
TAKE(
a,
,
-1
)
)+i*c(
B2:B6,
b
)
)
),
FILTER(
j,
IFERROR(
DROP(
j,
,
1
),
0
)<=m
)
)
)
),
VSTACK(
x,
@SORT(
FILTER(
y,
IFNA(
DROP(
y,
,
1
),
0
)=m
)
)
)
)
)
),
1
)
)Excel solution 13 for Calculate Cheapest Bottle Combination, proposed by Pieter de B.:
=DROP(
REDUCE(
A9:A12,
6-SEQUENCE(
5
),
LAMBDA(
a,
b,
LET(
i,
INDEX(
B2:B6,
b
),
c,
TAKE(
a,
,
1
)/i,
HSTACK(
TAKE(
a,
,
1
)-i*INT(
c
),
IFERROR(
DROP(
a,
,
1
),
0
)+INDEX(
C2:C6,
b
)*INT(
c
)
)
)
)
),
,
1
)Excel solution 14 for Calculate Cheapest Bottle Combination, proposed by JvdV -:
=LET(
x,
LAMBDA(
f,
a,
b,
IF(
a,
LET(
c,
XLOOKUP(
a,
B2:B6,
+B2:C6,
,
-1
),
f(
f,
TRUNC(
a-@c,
1
),
b+MAX(
c
)
)
),
b
)
),
MAP(
A9:A12,
LAMBDA(
s,
x(
x,
s,
)
)
)
)
TRUNC is in there because of a floating point issue. Try this in 4 cells in your Excel. Put 2.3 in A1,
then in A2 put =A1-2,
in A3 put =A2-0.1 and in A4 put =A3-0.1. Now set it to a 15-digit precision. And you'll see that A4 starts behaving wonky. I think this is what is described in the "near-zero results" section of the article below:
https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result
EDIT: Solved the "near-zero results" decimal point issue without TRUNC:
=LET(
x,
LAMBDA(
f,
a,
b,
IF(
a>0,
LET(
c,
XLOOKUP(
a,
B2:B6,
+B2:C6,
C2,
-1
),
f(
f,
a-@c,
b+MAX(
c
)
& )
),
b
)
),
MAP(
A9:A12,
LAMBDA(
s,
x(
x,
s,
)
)
)
)Excel solution 15 for Calculate Cheapest Bottle Combination, proposed by Burhan Cesur:
=DROP(REDUCE("",A9:A12,LAMBDA(s,v,VSTACK(s,LET(x,LOOKUP(v,$B$2:$B$6,$B$2:$B$6),QUOTIENT(v,x)*LOOKUP(v,$B$2:$B$6,$C$2:$C$6)+IF(MOD(v,x)=ROUND(MOD(v,x),0),IFNA(LOOKUP(MOD(v,x),$B$2:$B$6,$C$2:$C$6),0),(MOD(v,x)*10)*IFNA(LOOKUP(MOD(v,x),$B$2:$B$6,$C$2:$C$6),0)))))),1)Excel solution 16 for Calculate Cheapest Bottle Combination, proposed by Burhan Cesur:
=DROP(REDUCE("",A9:A12,LAMBDA(s,v,VSTACK(s,LET(
hedef,v,
fx, LAMBDA(fx,kalan,birikim,
IF(
kalan <= 0,
birikim,
LET(
uygun, IFERROR(FILTER(B2:B6, B2:B6 <= kalan, ""), 0),
en_uygun, IFERROR(IF(kalan<1,IF(ISNUMBER(FILTER(B2:B6,B2:B6=(kalan-MAX(uygun)))),MAX(uygun),MIN(uygun)),MAX(uygun)), 0),
fiyat, IF(en_uygun = 0, 0, INDEX(C2:C6, MATCH(en_uygun, B2:B6, 0))),
yeni_kalan, kalan - en_uygun,
yeni_uygun, IFERROR(FILTER(B2:B6, B2:B6 <= yeni_kalan, ""), 0),
IF(
OR(ROWS(uygun) = 0, en_uygun = 0),
birikim,
IF(
ROWS(yeni_uygun) = 0,
fx(fx, kalan - MIN(uygun), VSTACK(birikim, HSTACK(MIN(uygun), INDEX(C2:C6, MATCH(MIN(uygun), B2:B6, 0))))),
fx(fx, yeni_kalan, SUM(VSTACK(birikim, fiyat)))
)
)
)
)
),
fx(fx, hedef, "")
)))),1)Excel solution 17 for Calculate Cheapest Bottle Combination, proposed by Mehmet Çiçek:
=MAP(
A9:A12,
LAMBDA(
x,
LET(
b,
B2:B6,
i,
VLOOKUP(
x,
b,
1
),
SUM(
IFERROR(
LOOKUP(
--MID(
REPT(
i,
INT(
x/i
)
)&MOD(
x,
i
),
SEQUENCE(
10
),
1
),
b,
C2:C6
),
0
)
)
)
)
)Solving the challenge of Calculate Cheapest Bottle Combination with Python in Excel
Python in Excel solution 1 for Calculate Cheapest Bottle Combination, proposed by Aditya Kumar Darak 🇮🇳:
import math
df = xl("A1:C6", True)
qtys = xl("A8:A12", True)
df.columns = ["Bottle", "Capacity", "Cost"]
sort = df.sort_values(by="Capacity", ascending=False)
def MyFun(qty):
btl = [
[
bottle := math.floor(qty * 100 // (i * 100)),
qty := round(qty - bottle * i, 3),
][0]
for i in sort["Capacity"]
]
cost = (btl * sort["Cost"]).sum()
return cost
qtys["Cost"] = qtys["Input Litres"].map(MyFun)
qtys
Solving the challenge of Calculate Cheapest Bottle Combination with R
R solution 1 for Calculate Cheapest Bottle Combination, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/628 Bottle Price Optimization.xlsx"
input1 = read_excel(path, range = "A1:C6") %>% janitor::clean_names()
input2 = read_excel(path, range = "A8:A12")
test = read_excel(path, range = "A8:B12")
df = expand_grid(A = 0:10, B = 0:10, C = 0:10, D = 0:10, E = 0:10) %>%
mutate(rn = row_number()) %>%
pivot_longer(-rn, names_to = "capacity", values_to = "value") %>%
left_join(input1, by = c("capacity" = "bottle_type"), keep = T) %>%
mutate(cost = value * cost_bottle,
capacity = value * capacity_l) %>%
filter(capacity != 0) %>%
mutate(combo = paste0(value, "x", bottle_type)) %>%
summarise(total_cost = sum(cost),
total_capacity = sum(capacity),
combo = paste0(combo, collapse = ", "),
.by = rn)
total = input2 %>%
left_join(df, by = c("Input Litres" = "total_capacity")) %>%
mutate(lower_cost = min(total_cost, na.rm = T), .by = `Input Litres`) %>%
filter(total_cost == lower_cost)
all.equal(total$total_cost, test$`Answer Expected`, check.attributes = F)
# [1] TRUE
&&