What is the smallest sum which you can’t create using all or few of the coins given? Ex.1: Coins: 2, 4 1 can’t be created using given coins. Hence, smallest sum is 1. Ex. 2: Coins: 1, 2, 4 1 can be created with single coin 1 2 can be created with single coin 2 3 can be created with coins 1+2 4 can be created with single coin 4 5 can be created with coins 1+4 6 can be created with coins 2+4 7 can be created with coins 1+2+4 But 8 can’t be created. Hence smallest sum is 8.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 600
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Smallest Uncreatable Sum with Power Query
Power Query solution 1 for Find Smallest Uncreatable Sum, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(
A,
"Answer Expected",
each
let
a = List.Transform(Text.Split([Coins], ", "), Number.From),
b = List.Count(a),
c = List.Numbers(1, Number.Power(2, b) - 1),
d = List.Transform(
c,
each List.Sum(
List.Transform(
{0 .. b - 1},
(x) => if Number.BitwiseAnd(_, Number.Power(2, x)) <> 0 then a{x} else null
)
)
),
e = List.Difference({1 .. List.Max(d) + 1}, d){0}
in
e
)
in
BPower Query solution 2 for Find Smallest Uncreatable Sum, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Result",
each [
L = Expression.Evaluate("{" & [Coins] & "}"),
A = List.Accumulate(L, {}, (s, c) => s & {c} & List.Transform(s, (f) => f + c)),
M = List.Max(A) + 1,
Sq = {1 .. M},
D = List.Difference(Sq, A),
R = List.Min(D)
][R]
)
in
ReturnPower Query solution 3 for Find Smallest Uncreatable Sum, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
A = List.Transform(Text.Split([Coins], ", "), Number.From),
B =
let
Num = {1 .. Number.Power(2, List.Count(A))},
Base = 2,
Start = {},
G = (f, g, h) =>
let
a = Number.IntegerDivide(f, g),
b = Number.Mod(f, g),
c = h,
d = if f = 0 then (List.RemoveLastN(c & {a})) else @G(a, g, c & {b})
in
d,
D = List.Transform(Num, each G(_, Base, Start))
in
D,
e = List.Transform(B, each List.PositionOf(_, 1, 2)),
f = List.Transform(e, each List.Sum(List.Transform(_, (x) => A{x}?))),
g = List.Distinct(List.RemoveNulls(f)),
h = {g{0} .. List.Last(g)},
i = List.Difference(h, g),
j = if List.IsEmpty(i) then List.Last(g) + 1 else i{0}
in
j
)
in
SolSolving the challenge of Find Smallest Uncreatable Sum with Excel
Excel solution 1 for Find Smallest Uncreatable Sum, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
LAMBDA(
c,
LET(
r,
REDUCE(
0,
TEXTSPLIT(
c,
","
),
LAMBDA(
a,
v,
VSTACK(
a,
a+v
)
)
),
@UNIQUE(
VSTACK(
SEQUENCE(
MAX(
r
)+1
),
r
),
,
1
)
)
)
)Excel solution 2 for Find Smallest Uncreatable Sum, proposed by John V.:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
b,
REDUCE(
0,
TEXTSPLIT(
x,
","
),
LAMBDA(
a,
v,
VSTACK(
a,
a+v
)
)
),
@UNIQUE(
VSTACK(
SEQUENCE(
2+MAX(
b
)
)-1,
b
),
,
1
)
)
)
)Excel solution 3 for Find Smallest Uncreatable Sum, proposed by Kris Jaganah:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
DROP(
UNIQUE(
REDUCE(
0,
TEXTSPLIT(
x,
,
", "
),
LAMBDA(
x,
y,
VSTACK(
x,
x+y
)
)
)
),
1
),
b,
MAX(
a
),
c,
SEQUENCE(
b
),
d,
XLOOKUP(
c,
a,
a
),
IFERROR(
MIN(
FILTER(
c,
ISNA(
d
)
)
),
b+1
)
)
)
)Excel solution 4 for Find Smallest Uncreatable Sum, proposed by Julian Poeltl:
=MAP(
A2:A10,
LAMBDA(
C,
LET(
Per,
LAMBDA(
UptoNumber,
LET(
S,
SEQUENCE(
,
UptoNumber
),
DROP(
REDUCE(
"",
S,
LAMBDA(
A,
B,
LET(
C,
FILTER(
A,
LEN(
A
)=B-1
),
VSTACK(
A,
TOCOL(
IFS(
ISERR(
FIND(
S,
C
)
),
C&S
),
3
)
)
)
)
),
MAX(
S
)+1
)
)
),
N,
UNIQUE(
SORT(
VSTACK(
--TEXTSPLIT(
C,
,
","
),
MAP(
Per(
LEN(
C
)-LEN(
SUBSTITUTE(
C,
",",
""
)
)+1
),
LAMBDA(
A,
MAP(
A,
LAMBDA(
A,
SUM(
LET(
Af,
TEXTAFTER(
","&C,
",",
MID(
A,
SEQUENCE(
LEN(
A
)
),
1
)
),
--TEXTBEFORE(
Af,
",",
,
,
,
Af
)
)
)
)
)
)
)
)
)
),
XMATCH(
TRUE,
ISERROR(
XMATCH(
SEQUENCE(
MAX(
N
)+1
),
N
)
)
)
)
)
)Excel solution 5 for Find Smallest Uncreatable Sum, proposed by Julian Poeltl:
=MAP(A2:A10,LAMBDA(C,LET(N,UNIQUE(SORT(REDUCE(0,--TEXTSPLIT(C,","),LAMBDA(A,B,VSTACK(A,B,A+B))))),XMATCH(TRUE,ISERROR(XMATCH(SEQUENCE(MAX(N)+1),N))))))Excel solution 6 for Find Smallest Uncreatable Sum, proposed by Aditya Kumar Darak 🇮🇳:
=MAP(
A2:A10,
LAMBDA(
Input1,
LET(
split,
--TEXTSPLIT(
Input1,
,
", "
),
totals,
REDUCE(
,
split,
LAMBDA(
a,
b,
VSTACK(
a,
b,
a + b
)
)
),
seq,
SEQUENCE(
MAX(
totals
) + 1
),
stack,
VSTACK(
totals,
seq
),
notFnd,
UNIQUE(
stack,
,
TRUE
),
result,
MIN(
TAKE(
notFnd,
1
)
),
result
)
)
)Excel solution 7 for Find Smallest Uncreatable Sum, proposed by Timothée BLIOT:
=MAP(A2:A10,LAMBDA(z,LET(A,--REGEXEXTRACT(z,"d+",1),B,COLUMNS(A), C,A&CHAR(SEQUENCE(,B)+64),D,REDUCE(TOCOL(C),SEQUENCE(B-1),LAMBDA (w,v, LET(D,FILTER(w,LEN(w)=2*v), VSTACK(w,TOCOL(IF(ISERR(FIND( C,D)),D&C, 1/0),3)) ))),E,SORT(UNIQUE(MAP(D,LAMBDA(x, SUM(--REGEXEXTRACT(x, "d+",1)))))),F,SEQUENCE(ROWS(E)+1),TAKE(FILTER(F,NOT(IFNA(F=E,0))),1))))Excel solution 8 for Find Smallest Uncreatable Sum, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(
t,
LET(
_ts,
TEXTSPLIT(
t,
,
", "
) + 0,
_c,
DROP(
REDUCE(
"",
_ts,
LAMBDA(
a,
v,
VSTACK(
a,
a & "," & v
)
)
),
1
),
_sum,
UNIQUE(
MAP(
_c,
LAMBDA(
a,
SUM(
TEXTSPLIT(
a,
",",
,
1
) + 0
)
)
)
),
_s,
SEQUENCE(
MAX(
_sum
) - MIN(
_sum
) + 1,
,
MIN(
_sum
)
),
_x,
XMATCH(
_s,
_sum
),
IFERROR(
TAKE(
FILTER(
_s,
ISNA(
_x
)
),
1
),
MAX(
_s
) + 1
)
)
)
)Excel solution 9 for Find Smallest Uncreatable Sum, proposed by LEONARD OCHEA 🇷🇴:
=MAP(A2:A10,LAMBDA(x,LET(s,REDUCE(0,TEXTSPLIT(x,","),LAMBDA(a,b,VSTACK(a,a+b))),m,MAX(s),IFNA(XMATCH(0,N(UNIQUE(s)=SEQUENCE(m)-1))-1,m+1))))Excel solution 10 for Find Smallest Uncreatable Sum, proposed by Md. Zohurul Islam:
=MAP(
A2:A10,
LAMBDA(
z,
LET(
a,
0+TEXTSPLIT(
z,
,
", "
),
b,
REDUCE(
,
a,
LAMBDA(
x,
y,
LET(
p,
x+y,
q,
VSTACK(
x,
y,
p
),
q
)
& )
),
mx,
MAX(
b
)+1,
seq,
SEQUENCE(
mx
),
data,
UNIQUE(
VSTACK(
b,
seq
),
,
1
),
result,
MIN(
data
),
result
)
)
)Excel solution 11 for Find Smallest Uncreatable Sum, proposed by Philippe Brillault:
=MAP(_T,LAMBDA(z,LET(sq,SEQUENCE,set,TEXTSPLIT(z,,",")*1,n,ROWS(set),ti,MMULT(--MID(MAP(sq(2^n-1),LAMBDA(t,DEC2BIN(t,n))),sq(,n),1),set),@UNIQUE(VSTACK(ti,sq(MAX(ti)+1)),,1))))
(Thanks to Bo Rydobon for the tip on the last line, not to mention the use of REDUCE(0,TEXTSPLIT(c,“,”),LAMBDA(a,v,VSTACK(a,a+v))) which must be much faster than matrix product)Solving the challenge of Find Smallest Uncreatable Sum with Python
Python solution 1 for Find Smallest Uncreatable Sum, proposed by Konrad Gryczan, PhD:
import pandas as pd
import itertools
path = "600 Smallest Coin Sums.xlsx"
input = pd.read_excel(path, usecols="A", nrows=10)
test = pd.read_excel(path, usecols="B", nrows=10)
def find_lowest_impossible_sum(coins):
coins = list(map(int, coins.split(',')))
all_combinations = {sum(comb) for r in range(1, len(coins) + 1) for comb in itertools.combinations(coins, r)}
return next(i for i in range(1, sum(coins) + 2) if i not in all_combinations)
input['result'] = input['Coins'].apply(find_lowest_impossible_sum)
result = input[['result']]
print(result['result'].equals(test['Answer Expected'])) # True
Solving the challenge of Find Smallest Uncreatable Sum with Python in Excel
Python in Excel solution 1 for Find Smallest Uncreatable Sum, proposed by Alejandro Campos:
df = xl("A1:A10", headers=True)
def find_smallest_unreachable_sum(coins):
coins = list(map(int, coins.split(',')))
coins.sort()
smallest_unreachable_sum = 1
for coin in coins:
if coin > smallest_unreachable_sum:
break
smallest_unreachable_sum += coin
return smallest_unreachable_sum
df['Smallest_Unreachable_Sum'] = df['Coins'].apply(find_smallest_unreachable_sum)
df
Python in Excel solution 2 for Find Smallest Uncreatable Sum, proposed by Anshu Bantra:
def find_smallest_unreachable_sum(coins):
coins.sort()
smallest_unreachable_sum = 1
for coin in coins:
if coin > smallest_unreachable_sum:
break
smallest_unreachable_sum += coin
return smallest_unreachable_sum
df = xl("A1:A10", headers=True)
[find_smallest_unreachable_sum(list(map(int, _.split(', ')))) for _ in df['Coins']]
Solving the challenge of Find Smallest Uncreatable Sum with R
R solution 1 for Find Smallest Uncreatable Sum, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/600 Smallest Coin Sums.xlsx"
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B10")
find_lowest_impossible_sum = function(coins) {
coins = as.numeric(str_split(coins, ",")[[1]])
all_combinations = unlist(map(1:length(coins), ~ combn(coins, .x, sum, simplify = TRUE)))
diff = setdiff(seq(min(coins), sum(coins)), all_combinations)
if (length(diff) == 0) sum(coins) + 1 else diff[1]
}
result = input %>%
mutate(result = map_dbl(Coins, find_lowest_impossible_sum)) %>%
select(result)
all.equal(result$result, test$`Answer Expected`)
#> [1] TRUE
&&