For the given color bands in Column E, work out the total resistance. Color bands have color codes given in columm B. They are assigned values through 0 to 9 sequentially. RedOrangeGreen is written as reorgr in color codes. Last color code is for how many times 0s will appear. Ex. reorgr = re & or & gr = 2 & 3 & (5 times 0s) = 2300000 bugyvibl = bu & gy & vi & bl = 6 & 8 & 7 & (0 times 0s) = 687
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 420
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Decode Color Band Resistance with Power Query
Power Query solution 1 for Decode Color Band Resistance, proposed by Aditya Kumar Darak 🇮🇳:
let
Lookup = Excel.CurrentWorkbook(){[Name = "tblLookup"]}[Content],
ColourBand = Excel.CurrentWorkbook(){[Name = "tblClrBnd"]}[Content],
Return = Table.AddColumn(
ColourBand,
"Answer",
each [
S = Splitter.SplitTextByRepeatedLengths(2)([Color Bands]),
O1 = List.Transform(
List.RemoveLastN(S, 1),
(f) => Text.From(List.PositionOf(Lookup[Code], f))
),
O2 = Text.Repeat("0", List.PositionOf(Lookup[Code], List.Last(S))),
R = Text.Combine(O1) & O2
][R]
)
in
ReturnPower Query solution 2 for Decode Color Band Resistance, proposed by Luan Rodrigues:
let
Fonte = Tabela2,
tab1 = Table.AddIndexColumn(Tabela1, "Ind", 0, 1),
res = Table.AddColumn(
Fonte,
"Personalizar",
each Text.Combine(
let
a = List.Transform(
Splitter.SplitTextByRepeatedLengths(2)([Color Bands]),
each Table.SelectRows(tab1, (x) => x[Code] = _)[Ind]{0}
),
b =
if List.Last(a) = 0 then
List.FirstN(a, List.Count(a) - 1)
else
List.FirstN(a, List.Count(a) - 1) & List.Repeat({0}, List.Last(a))
in
List.Transform(b, Text.From)
)
)
in
resPower Query solution 3 for Decode Color Band Resistance, proposed by Ramiro Ayala Chávez:
let
t1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
t2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
L = List.Transform,
a = Table.AddIndexColumn(t1, "D"),
b = L(t2[Color Bands], each Splitter.SplitTextByRepeatedLengths(2)(_)),
c = L(b, each Table.FromColumns({_})),
d = L(c, each Table.AddColumn(_, "N", each a[D]{List.PositionOf(a[Code], [Column1])})[N]),
e = L(
d,
each
if List.Last(_) = 0 then
List.RemoveLastN(_)
else
List.RemoveLastN(List.InsertRange(_, List.Count(_) - 1, List.Repeat({0}, List.Last(_))))
),
Sol = Table.FromColumns(
{t2[Color Bands], L(e, each Text.Combine(L(_, each Text.From(_))))},
Table.ColumnNames(t2) & {"Answer Expected"}
)
in
SolPower Query solution 4 for Decode Color Band Resistance, proposed by Udit Chatterjee:
let
fxCustomSplitter = (string as text, splitsAt as number) =>
let
textLength = Text.Length(string),
splitPositions = List.Generate(() => 0, each _ < textLength, each _ + splitsAt),
splittedStringList = Splitter.SplitTextByPositions(splitPositions)(string),
textTypeSplittedList = List.Transform(splittedStringList, each Text.From(_))
in
textTypeSplittedList,
fxListMapper = (mainList as list, refList as list) =>
let
lastCode = List.Last(mainList),
subList = List.RemoveLastN(mainList, 1),
mappedList = List.Transform(subList, each Text.From(List.PositionOf(refList, _))),
mappedLastCode = Text.Repeat("0", List.PositionOf(refList, lastCode)),
completeMappedList = List.Combine({mappedList, {mappedLastCode}}),
MappedText = Text.Combine(completeMappedList)
in
MappedText,
Source = ch2024_01_60,
colorCodes = Source[Code],
colorBands = List.RemoveItems(Source[Color Bands], {"", null}),
colorBandCodes = List.Transform(colorBands, each @fxCustomSplitter(_, 2)),
colorBandIndexCodes = List.Transform(colorBandCodes, each @fxListMapper(_, colorCodes))
in
colorBandIndexCodesSolving the challenge of Decode Color Band Resistance with Excel
Excel solution 1 for Decode Color Band Resistance, proposed by Bo Rydobon 🇹🇭:
=MAP(
D2:D10,
LAMBDA(
a,
LET(
x,
XMATCH(
MID(
a,
SEQUENCE(
LEN(
a
)/2,
,
,
2
),
2
),
B2:B11
)-1,
CONCAT(
DROP(
x,
-1
)
)*10^TAKE(
x,
-1
)
)
)
)Excel solution 2 for Decode Color Band Resistance, proposed by Rick Rothstein:
=MAP(D2:D10,
LAMBDA(d,
LET(b,
B2:B11,
c,
LEN(
d
)-2,
SUBSTITUTE(REDUCE(LEFT(
d,
c
),
SEQUENCE((c)/2,
,
,
2),
LAMBDA(
a,
x,
REPLACE(
a,
x,
2,
"/"&XMATCH(
MID(
d,
x,
2
),
b
)-1
)
)),
"/",
"")&REPT(
0,
XMATCH(
RIGHT(
d,
2
),
b
)-1
))))Excel solution 3 for Decode Color Band Resistance, proposed by Rick Rothstein:
=MAP(
D2:D10,
LAMBDA(
d,
LET(
b,
B2:B11,
r,
RIGHT(
d,
2
),
m,
MID(
d,
SEQUENCE(
LEN(
d
)/2,
,
,
2
),
2
),
SUBSTITUTE(
REDUCE(
d,
DROP(
m,
-1
),
LAMBDA(
a,
x,
SUBSTITUTE(
a,
x,
XMATCH(
x,
b
)-1,
1
)
)
),
r,
REPT(
0,
XMATCH(
r,
b
)-1
)
)
)
)
)Excel solution 4 for Decode Color Band Resistance, proposed by John V.:
=MAP(
D2:D10,
LAMBDA(
x,
LET(
b,
TOCOL(
XMATCH(
MID(
x,
2*ROW(
1:10
)-1,
2
),
B2:B11
)-1,
2
),
CONCAT(
DROP(
b,
-1
),
REPT(
0,
TAKE(
b,
-1
)
)
)
)
)
)Excel solution 5 for Decode Color Band Resistance, proposed by محمد حلمي:
=MAP(
D2:D10,
LAMBDA(
a,
LET(
i,
XLOOKUP(
MID(
a,
SEQUENCE(
LEN(
a
)/2,
,
,
2
),
2
),
B2:B11,
SEQUENCE(
10
)-1
),
CONCAT(
DROP(
i,
-1
),
REPT(
0,
TAKE(
i,
-1
)
)
)
)
)
)Excel solution 6 for Decode Color Band Resistance, proposed by 🇰🇷 Taeyong Shin:
=MAP(
D2:D10,
LAMBDA(
x,
LET(
c,
CONCAT(
XMATCH(
MID(
x,
SEQUENCE(
LEN(
x
)/2,
,
,
2
),
2
),
B2:B11
)-1
),
REPLACE(
c,
LEN(
c
),
1,
)&REPT(
0,
RIGHT(
c
)
)
)
)
)Excel solution 7 for Decode Color Band Resistance, proposed by Kris Jaganah:
=MAP(
D2:D10,
LAMBDA(
x,
LET(
a,
B2:B11,
b,
XLOOKUP(
MID(
x,
SEQUENCE(
LEN(
x
)/2,
,
,
2
),
2
),
a,
XMATCH(
a,
a
)-1
),
CONCAT(
DROP(
b,
-1
)
)&REPT(
0,
TAKE(
b,
-1
)
)
)
)
)Excel solution 8 for Decode Color Band Resistance, proposed by Julian Poeltl:
=MAP(
D2:D10,
LAMBDA(
CB,
LET(
CC,
$B$2:$B$11,
M,
XMATCH(
MID(
CB,
SEQUENCE(
,
LEN(
CB
)/2,
,
2
),
2
),
CC
)-1,
CONCAT(
DROP(
M,
,
-1
),
REPT(
0,
TAKE(
M,
,
-1
)
)
)
)
)
)Excel solution 9 for Decode Color Band Resistance, proposed by Timothée BLIOT:
=MAP(
D2:D10,
LAMBDA(
z,
LET(
A,
XLOOKUP(
MID(
z,
SEQUENCE(
LEN(
z
)/2,
,
,
2
),
2
),
B2:B11,
SEQUENCE(
10
)-1
),
CONCAT(
DROP(
A,
-1
)
)&REPT(
"0",
TAKE(
A,
-1
)
)
)
)
)Excel solution 10 for Decode Color Band Resistance, proposed by Hussein SATOUR:
=MAP(
D2:D10,
LAMBDA(
x,
LET(
a,
XLOOKUP(
MID(
x,
SEQUENCE(
LEN(
x
)/2,
,
,
2
),
2
),
B2:B11,
SEQUENCE(
10
)-1
),
CONCAT(
DROP(
a,
-1
),
REPT(
0,
TAKE(
a,
-1
)
)
)
)
)
)Excel solution 11 for Decode Color Band Resistance, proposed by Oscar Mendez Roca Farell:
=MAP(
REDUCE(
D2:D10,
ROW(
1:10
),
LAMBDA(
i,
x,
SUBSTITUTE(
i,
INDEX(
B2:B11,
x
),
x-1
)
)
),
LAMBDA(
a,
LEFT(
a,
LEN(
a
)-1
)&REPT(
0,
RIGHT(
a
)
)
)
)Excel solution 12 for Decode Color Band Resistance, proposed by Sunny Baggu:
=MAP(
D2:D11,
LAMBDA(
x,
LET(
_m,
MID(
x,
SEQUENCE(
10,
,
1,
2
),
2
),
_n,
TOCOL(
XLOOKUP(
_m,
B2:B11,
SEQUENCE(
10,
,
0
)
),
3
),
IFERROR(
CONCAT(
DROP(
_n,
-1
)
) & REPT(
0,
TAKE(
_n,
-1
)
),
""
)
)
)
)Excel solution 13 for Decode Color Band Resistance, proposed by LEONARD OCHEA 🇷🇴:
=MAP(
D2:D10,
LAMBDA(
a,
LET(
l,
LEN(
a
),
c,
CONCAT(
XMATCH(
MID(
a,
& SEQUENCE(
l/2,
,
,
2
),
2
),
B2:B11
)-1
),
LEFT(
c,
l/2-1
)&REPT(
0,
RIGHT(
c
)
)
)
)
)Excel solution 14 for Decode Color Band Resistance, proposed by Abdallah Ally:
=MAP(
D2:D10,
LAMBDA(
x,
LET(
a,
x,
b,
B2:B11,
c,
CONCAT(
MATCH(
MID(
a,
SEQUENCE(
LEN(
a
)/2,
,
,
2
),
2
),
b,
0
)-1
),
LEFT(
c,
LEN(
c
)-1
)&REPT(
0,
RIGHT(
c
)
)
)
)
)Excel solution 15 for Decode Color Band Resistance, proposed by Pieter de B.:
*10^TAKE(
x,
-1
) love it!Excel solution 16 for Decode Color Band Resistance, proposed by Asheesh Pahwa:
=LET(
a,
B2:B11,
b,
SEQUENCE(
10,
,
0
),
MAP(
D2:D10,
LAMBDA(
x,
LET(
m,
WRAPROWS(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
2
),
br,
BYROW(
m,
LAMBDA(
y,
CONCAT(
y
)
)
),
xl,
XLOOKUP(
br,
a,
b
),
CONCAT(
DROP (
xl,
-1
),
REPT(
0,
TAKE(
xl,
-1
)
)
)
)
)
)
)Excel solution 17 for Decode Color Band Resistance, proposed by Charles Roldan:
=LET(
Code,
B2:B11,
Bands,
D2:D10,
MAP(
Bands,
LAMBDA(
b,
LET(
s,
XMATCH(
MID(
b,
SEQUENCE(
LEN(
b
) / 2,
,
1,
2
),
2
),
Code
) - 1,
CONCAT(
DROP(
s,
-1
)
) & REPT(
"0",
TAKE(
s,
-1
)
)
)
)
)
)Excel solution 18 for Decode Color Band Resistance, proposed by Andy Heybruch:
=MAP(
D2:D10,
LAMBDA(
a,
LET(
_a,
a,
_len,
LEN(
_a
)/2,
_clrs,
XMATCH(
MID(
_a,
SEQUENCE(
,
_len,
1,
2
),
2
),
$B$2:$B$11,
0
)-1,
CONCAT(
DROP(
_clrs,
,
-1
),
REPT(
"0",
TAKE(
_clrs,
,
-1
)
)
)
)
)
)Excel solution 19 for Decode Color Band Resistance, proposed by Giorgi Goderdzishvili:
=MAP(
F2:F10,
LAMBDA(
x,
LET(
sp,
MID(
x,
SEQUENCE(
,
LEN(
x
)/2,
1,
2
),
2
),
xm,
XMATCH(
sp,
$C$2:$C$11,
0
)-1,
CONCAT(
DROP(
xm,
,
-1
),
REPT(
"0",
TAKE(
xm,
,
-1
)
)
)*1
)
)
)Excel solution 20 for Decode Color Band Resistance, proposed by Sandeep Marwal:
=LET(
initial,
CONCAT(
XLOOKUP(
MID(
E2,
SEQUENCE(
LEN(
E2
)/2,
,
1,
2
),
2
),
B:B,
C:C
)
),
Linitial,
LEFT(
initial,
LEN(
initial
)-1
),
Rinitial,
RIGHT(
initial,
1
),
VALUE(
CONCAT(
Linitial,
REPT(
0,
Rinitial
)
)
)
)Excel solution 21 for Decode Color Band Resistance, proposed by Diarmuid Early:
=MAP(
D2:D10,
LAMBDA(
cd,
LET(
cdList,
MID(
cd,
SEQUENCE(
LEN(
cd
)/2
),
2
),
nums,
XMATCH(
cdList,
B2:B11
)-1,
CONCAT(
DROP(
nums,
-1
),
REPT(
0,
@TAKE(
nums,
-1
)
)
)
)
)
)
The idea:
* MAP = do this for each input code
* Split the string into 2-letter codes with MID
* Match them against the code list (minus one for zero base)
* CONCAT to combine all the elements of the list except the last,
and REPT to add that number of zeroes at the end. (I’m not sure if the @ is needed here,
but better safe!)Excel solution 22 for Decode Color Band Resistance, proposed by Burhan Cesur:
=MAP(D2:D10,
LAMBDA(k,
LET(x,
REDUCE(
k,
B2:B11,
LAMBDA(
a,
v,
SUBSTITUTE(
a,
v,
XMATCH(
v,
B2:B11,
0
)-1
)
)
),
y,
LEFT(x,
(LEN(
k
)-2)/2),
z,
REPT(
0,
RIGHT(
x,
1
)
),
CONCAT(
y,
z
))))Excel solution 23 for Decode Color Band Resistance, proposed by Josh Brodrick:
=LET(
a,
B2:B11,
b,
D2:D10,
MAP(
MAP(
b,
LAMBDA(
y,
MID(
y,
1,
LEN(
y
)-2
)
)
),
LAMBDA(
z,
CONCAT(
IFNA(
MATCH(
MID(
z,
SEQUENCE(
1,
LEN(
z
),
,
2
),
2
),
a,
0
)-1,
""
)
)
)
)&REPT(
0,
MATCH(
MAP(
b,
LAMBDA(
x,
RIGHT(
x,
2
)
)
),
a,
0
)-1
)
)Excel solution 24 for Decode Color Band Resistance, proposed by Tyler Cameron:
=LET(
a,
B2:B11,
b,
D2:D10,
MAP(
b,
LAMBDA(
x,
LET(
y,
MATCH(
MID(
x,
SEQUENCE(
LEN(
x
)/2,
,
,
2
),
2
),
a,
)-1,
z,
COUNTA(
y
),
CONCAT(
INDEX(
y,
SEQUENCE(
z-1
)
),
REPT(
"0",
--INDEX(
y,
z
)
)
)
)
)
)
)Excel solution 25 for Decode Color Band Resistance, proposed by Alexandra Popoff:
= LAMBDA(
In_Input,
DB_color_code,
[In_color_numb],
LET(
DB_color_numb,
if(
isomitted(
In_color_numb
),
SEQUENCE(
ROWS(
DB_color_code
),
1,
0,
1
),
In_color_numb
),
BYROW(
In_input,
LAMBDA(
z_in,
LET(
lst_code,
BYROW(
SEQUENCE(
LEN(
z_in
) / 2,
1,
1,
1
),
LAMBDA(
z_i,
RIGHT(
LEFT(
z_in,
z_i * 2
),
2
)
)
),
lst_numb,
XLOOKUP(
lst_code,
DB_color_code,
DB_color_numb
),
VALUE(
TEXTJOIN(
"",
TRUE,
VSTACK(
DROP(
lst_numb,
-1
),
IFERROR(
SEQUENCE(
VALUE(
INDEX(
TAKE(
lst_numb,
-1
),
1,
1
)
),
1,
0,
0
),
""
)
)
)
)
)
)
)
)
)Excel solution 26 for Decode Color Band Resistance, proposed by Talia Cao, CPA:
=MAP(D2:D10, LAMBDA(Band,
LET(
Codes, MID(Band, SEQUENCE(LEN(Band) / 2, , , 2), 2),
Vals, XMATCH(Codes, B2:B11) - 1,
CONCAT(DROP(Vals, -1), REPT(0, TAKE(Vals, -1)))
)))Excel solution 27 for Decode Color Band Resistance, proposed by Ben Gutscher:
=LET(
codes,
HSTACK(
$A$2:$B$11,
SEQUENCE(
10,
,
0
)
),
colors,
MID(
D2,
SEQUENCE(
,
LEN(
D2
)/2,
1,
2
),
2
),
prelim,
CONCAT(
BYCOL(
colors,
LAMBDA(
c,
XLOOKUP(
c,
INDEX(
codes,
0,
2
),
INDEX(
codes,
0,
3
)
)
)
)
),
LEFT(
prelim,
LEN(
prelim
)-1
)&REPT(
"0",
RIGHT(
prelim
)
)
)Excel solution 28 for Decode Color Band Resistance, proposed by Peter Compton:
=BYROW(
D9:D17,
LAMBDA(
x,
TEXTJOIN(
"",
,
DROP(
XLOOKUP(
MID(
x,
SEQUENCE(
LEN(
x
)/2,
,
1,
2
),
2
),
$B$9:$B$18,
SEQUENCE(
10,
,
0
)
),
-1
),
REPT(
0,
TAKE(
XLOOKUP(
MID(
x,
SEQUENCE(
LEN(
x
)/2,
,
1,
2
),
2
),
$B$9:$B$18,
& SEQUENCE(
10,
,
0
)
),
-1
)
)
)
)
)Solving the challenge of Decode Color Band Resistance with Python in Excel
Python in Excel solution 1 for Decode Color Band Resistance, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_420 - Resistor Value.xlsx'
df = pd.read_excel(file_path, usecols='B')
df['ind'] = df.index.astype(str) # Add color codes (numbers) column
df2 = pd.read_excel(file_path, usecols='D:E', nrows=9)
def resistor_value(dataframe, col):
values = [col[i: i + 2] for i in range(0, len(col), 2)]
for i in range(len((values))):
for j in dataframe.index:
if dataframe.iat[j, 0] == values[i]:
values[i] = dataframe.iat[j, 1]
values = ''.join(values)
return values[: -1] + '0' * int(values[-1])
df2['My Answer'] = df2.apply(lambda x: resistor_value(df, x['Color Bands']), axis=1)
print(df2)
Python in Excel solution 2 for Decode Color Band Resistance, proposed by ferhat CK:
Code = ["bl", "br", "re", "or", "ye", "gr", "bu", "vi", "gy", "wh"]
df =xl("D1:D10", headers=True)
def convert(s):
index = ''
for i in range(0, len(s), 2):
harf = s[i:i+2]
index += str(Code.index(harf))
index = index[:-1] + '0' * int(index[-1])
if s.endswith('re'):
index = index[:-2] + '00'
return int(index)
df['Answer Expected'] = df['Color Bands'].apply(convert)
df
Solving the challenge of Decode Color Band Resistance with R
R solution 1 for Decode Color Band Resistance, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input1 = read_excel("Excel/420 Resistor Value.xlsx", range = "A1:B11")
input2 = read_excel("Excel/420 Resistor Value.xlsx", range = "D1:D10")
test = read_excel("Excel/420 Resistor Value.xlsx", range = "E1:E10")
find_resistance = function(bands, input) {
codes = input %>%
mutate(code = 0:9)
pairs = strsplit(bands, "")[[1]]
pairs = matrix(pairs, ncol = 2, byrow = TRUE) %>%
as.data.frame() %>%
unite("pair", V1, V2, sep = "") %>%
left_join(codes, by = c("pair" = "Code")) %>%
mutate(nr = rev(row_number()))
last = pairs[nrow(pairs),] %>%
mutate(res = 10^code) %>%
pull(res)
pairs_wol = pairs[-nrow(pairs),] %>%
mutate(res = code*10^(nr-2)) %>%
pull(res)
final_res = sum(pairs_wol) * last
return(final_res)
}
result = input2 %>%
mutate(`Answer Expected` = map_dbl(`Color Bands`, find_resistance, input1) %>%
as.character())
&