Now, let’s express the derived values as K, M or G (Kilo, Mega or Giga). For the given color bands in Column E, work out the total resistance. Color bands have color codes given in column 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 The values need to be expressed in K (Kilo) / M (Mega) / G (Giga) Ohm or without these only in Ohm. At least one digit or at most 3 digits should be on left side of decimal if can be expressed in K/M/G. So basically, this is equivalent to thousand, million and billion formatting of currencies. 123 = 123 Ohm 1234 = 1.234 K Ohm 12345 = 12.345 K Ohm 123456 = 123.456 K Ohm 1234567 = 1.234567 M Ohm 1234567893 = 1.234567893 G Ohm
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 438
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Convert Resistance to Ohms Unit with Power Query
Power Query solution 1 for Convert Resistance to Ohms Unit, proposed by Bo Rydobon 🇹🇭:
let
Code = Excel.CurrentWorkbook(){[Name = "Code"]}[Content],
Color = Table.TransformColumns(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{
"Color Bands",
each
let
n = List.Accumulate(
Table.ToRows(Code),
_,
(s, l) => Text.Replace(s, l{1}, Text.From(l{2}))
),
l = Text.Length(n) - 1,
r = Number.From(Text.End(n, 1)),
m = Number.IntegerDivide(l + r - 1, 3)
in
Text.Trim(
Text.ReplaceRange(Text.From(Number.From(Text.Start(n, l))), l - m * 3 + r, 0, "."),
"."
)
& List.ReplaceMatchingItems({m}, {{0, ""}, {1, " K"}, {2, " M"}, {3, " G"}}){0}
& " Ohm"
}
)
in
ColorPower Query solution 2 for Convert Resistance to Ohms Unit, proposed by Aditya Kumar Darak 🇮🇳:
let
Data = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Lookup = Excel.CurrentWorkbook(){[Name = "lookup"]}[Content][Code],
Format = Table.FromColumns(
{{1, 1000, 1000000, 1000000000}, {"Ohm", "K Ohm", "M Ohm", "G Ohm"}},
{"N", "F"}
),
Return = Table.AddColumn(
Data,
"Answer",
each [
S = Splitter.SplitTextByRepeatedLengths(2)([Color Bands]),
V = List.Transform(S, (f) => Text.From(List.PositionOf(Lookup, f))),
F = List.RemoveLastN(V, 1),
L = Number.From(List.Last(V)),
C = Number.From(Text.Combine(F) & Text.Repeat("0", L)),
LT = Table.Last(Table.SelectRows(Format, (f) => f[N] <= C)),
R = Text.From(C / LT[N]) & " " & LT[F]
][R]
)
in
ReturnPower Query solution 3 for Convert Resistance to Ohms Unit, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Bands = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Sol = Table.AddColumn(
Bands,
"A",
each
let
a = List.Transform(List.Split(Text.ToList([Color Bands]), 2), Text.Combine),
b = List.Zip(List.Skip(Table.ToColumns(Source))),
c = List.ReplaceMatchingItems(a, b),
d = List.FirstN(c, List.Count(c) - 1) & List.Repeat({0}, List.Last(c)),
e = Text.Combine(List.Transform(d, each Text.From(_))),
f = Text.Length(e),
g =
if f < 4 then
e & " Ohm"
else if f > 3 and f < 7 then
Text.From(Number.From(e) / 1000) & " K Ohm"
else if f > 6 and f < 10 then
Text.From(Number.From(e) / 1000000) & " M Ohm"
else
Text.From(Number.From(e) / 1000000000) & " G Ohm"
in
g
)
in
SolSolving the challenge of Convert Resistance to Ohms Unit with Excel
Excel solution 1 for Convert Resistance to Ohms Unit, proposed by Bo Rydobon 🇹🇭:
=LET(n,
REDUCE(
E2:E10,
B2:B11,
LAMBDA(
a,
v,
SUBSTITUTE(
a,
v,
ROWS(
v:B2
)-1
)
)
),
l,
LEN(
n
)-1,
r,
RIGHT(
n
),
m,
FLOOR(
r+l-1,
3
),
LEFT(
n,
l
)*10^(r-m)&CHOOSE(
m/3+1,
"",
" K",
" M",
" G"
)&" Ohm")Excel solution 2 for Convert Resistance to Ohms Unit, proposed by John V.:
=MAP(
E2:E10,
LAMBDA(
x,
LET(
b,
XMATCH(
MID(
x,
2*SEQUENCE(
LEN(
x
)/2
)-1,
2
),
B2:B11
)-1,
n,
CONCAT(
DROP(
b,
-1
),
REPT(
0,
TAKE(
b,
-1
)
)
),
c,
FLOOR(
LEN(
n
)-1,
3
),
n/10^c&CHOOSE(
1+c/3,
"",
" K",
" M",
" G"
)&" Ohm"
)
)
)Excel solution 3 for Convert Resistance to Ohms Unit, proposed by محمد حلمي:
=MAP(
E2:E10,
LAMBDA(
a,
LET(
i,
XLOOKUP(
MID(
a,
SEQUENCE(
LEN(
a
)/2,
,
,
2
),
2
),
B2:B11,
C2:C11
),
k,
--CONCAT(
DROP(
i,
-1
),
REPT(
0,
TAKE(
i,
-1
)
)
),
SWITCH(
MATCH(
k,
10^{0,
3,
6,
9}
),
1,
k,
2,
k/10^3&" K",
3,
k/10^6&" M",
k/10^9&" G"
)&" Ohm"
)
)
)Excel solution 4 for Convert Resistance to Ohms Unit, proposed by Kris Jaganah:
=MAP(E2:E10,
LAMBDA(x,
LET(a,
XLOOKUP(
MID(
x,
SEQUENCE(
LEN(
x
)/2,
,
,
2
),
2
),
B2:B11,
C2:C11
),
b,
--(CONCAT(
DROP(
a,
-1
)
)&REPT(
0,
TAKE(
a,
-1
)
)),
c,
LEN(
b
),
IFS(c<4,
b,
c<7,
b/1000&" K",
c<10,
b/(10^6)&" M",
1,
b/(10^9)&" G")&" Ohm")))Excel solution 5 for Convert Resistance to Ohms Unit, proposed by Konrad Gryczan, PhD:
= int(number)Excel solution 6 for Convert Resistance to Ohms Unit, proposed by Julian Poeltl:
=MAP(E2:E10,
LAMBDA(C,
LET(T,
B2:C11,
X,
VLOOKUP(
MID(
C,
SEQUENCE(
LEN(
C
)/2,
,
,
2
),
2
),
T,
2,
FALSE
),
A,
CONCAT(
DROP(
X,
-1
),
REPT(
0,
TAKE(
X,
-1
)
)
),
L,
ROUNDDOWN((LEN(
A
)-1)/3,
0),
A/(1&REPT(
0,
L*3
))&CHOOSE(
L+1,
"",
" K",
" M",
" G"
)&" Ohm")))Excel solution 7 for Convert Resistance to Ohms Unit, proposed by Timothée BLIOT:
=MAP(E2:E10,
LAMBDA(z,
LET(A,
XLOOKUP(
MID(
z,
SEQUENCE(
LEN(
z
)/2,
,
,
2
),
2
),
B2:B11,
SEQUENCE(
10
)-1
),
B,
--(CONCAT(
DROP(
A,
-1
)
)&REPT(
"0",
TAKE(
A,
-1
)
)),
C,
{10;7;4;1},
D,
LEN(
B
),
(B/10^XLOOKUP(
D,
C,
C-1,
,
-1
))&XLOOKUP(
D,
C,
{" G";" M";" K";""},
,
-1
)&" Ohm")))Excel solution 8 for Convert Resistance to Ohms Unit, proposed by Hussein SATOUR:
=MAP(E2:E10,
LAMBDA(x,
LET(a,
LEN(
x
)/2,
b,
XLOOKUP(
MID(
x,
SEQUENCE(
a,
,
,
2
),
2
),
B2:B11,
$C$2:$C$11
),
c,
TAKE(
b,
-1
),
d,
ROUNDDOWN((a-2+c)/3,
0),
(CONCAT(
DROP(
b,
-1
),
REPT(
0,
c
)
)/1000^d)&" "&CHOOSE(
d+1,
"",
"K",
"M",
"G"
)&" Ohm")))Excel solution 9 for Convert Resistance to Ohms Unit, proposed by Oscar Mendez Roca Farell:
=MAP(REDUCE(
F2:F10,
ROW(
1:10
),
LAMBDA(
i,
x,
SUBSTITUTE(
i,
INDEX(
C2:C11,
x
),
x-1
)
)
),
LAMBDA(a,
LET(n,
LEFT(
a,
LEN(
a
)-1
)&REPT(
0,
RIGHT(
a
)
),
v,
n/10^(3*ROW(
1:3
)),
t,
INT(
v
),
IFERROR(TOCOL(IFS((t>0)*(LEN(
t
)<4),
v&" "&{"K";"M";"G"}),
2),
n)&" Ohm")))Excel solution 10 for Convert Resistance to Ohms Unit, proposed by Sunny Baggu:
=MAP(
E2:E10,
LAMBDA(
x,
LET(
a,
{0; 4; 7; 10},
b,
{1; 1000; 1000000; 1000000000},
c,
{"Ohm"; "K Ohm"; "M Ohm"; "G Ohm"},
_t,
MID(
x,
SEQUENCE(
20,
,
,
2
),
2
),
_v,
TOCOL(
XLOOKUP(
_t,
B2:B11,
C2:C11
),
3
),
_n,
CONCAT(
DROP(
_v,
-1
)
) & REPT(
0,
TAKE(
_v,
-1
)
),
_n / XLOOKUP(
LEN(
_n
),
a,
b,
,
-1
) & " " & XLOOKUP(
LEN(
_n
),
a,
c,
,
-1
)
)
)
)Excel solution 11 for Convert Resistance to Ohms Unit, proposed by LEONARD OCHEA 🇷🇴:
=LET(
u,
" Ohm",
x,
BYROW(
XLOOKUP(
MID(
E2:E10,
SEQUENCE(
,
10,
,
2
),
2
),
B2:B11,
C2:C11,
""
),
CONCAT
),
n,
LEFT(
x,
LEN(
x
)-1
),
m,
n*10^MOD(
x,
10
),
IFS(
m<10^3,
m&u,
m<10^6,
m/10^3&" K"&u,
m<10^9,
m/10^6&" M"&u,
1,
m/10^9&" G"&u
)
)Excel solution 12 for Convert Resistance to Ohms Unit, proposed by 🇵🇪 Ned Navarrete C.:
=MAP(
E2:E10,
LAMBDA(
r,
LET(
d,
LEN(
r
)/2,
i,
XMATCH(
MID(
r,
SEQUENCE(
d,
,
,
2
),
2
),
B2:B11
)-1,
r,
TAKE(
i,
-1
),
n,
CONCAT(
DROP(
i,
-1
)
)&REPT(
0,
r
),
s,
FLOOR(
d+r-2,
3
),
n/10^s&CHOOSE(
s/3+1,
"",
" K",
" M",
" G"
)&" Ohm"
)
)
)Excel solution 13 for Convert Resistance to Ohms Unit, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=MAP(
E2:E10,
LAMBDA(
cb,
LET(
c,
XLOOKUP(
MID(
cb,
SEQUENCE(
LEN(
cb
)/2,
,
1,
2
),
2
),
$B$2:$B$11,
$C$2:$C$11
),
n,
CONCAT(
DROP(
c,
-1
),
REPT(
"0",
TAKE(
c,
-1
)
)
),
IF(
--n<100000,
TEXT(
n,
"[<1000]0"" Ohm"";[<1000000]0,###."" K Ohm"""
),
TEXT(
n,
"[<1000000000]0,######.."" M Ohm"";0,#########..."" G Ohm"""
)
)
)
)
)Solving the challenge of Convert Resistance to Ohms Unit with Python in Excel
Python in Excel solution 1 for Convert Resistance to Ohms Unit, proposed by ferhat CK:
Code = ["bl", "br", "re", "or", "ye", "gr", "bu", "vi", "gy", "wh"]
df =xl("E1:E10", 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'
if len(index)<4:
index=index+ " Ohm"
return index
if len(index)<7:
index=str(int(index)/1000) + " K Ohm"
return index
if len(index)<10:
index=str(int(index)/1000000)+ " M Ohm"
return index
if len(index)<13:
index=str(int(index)/1000000000)+ " G Ohm"
return index
df['Answer Expected'] = df['Color Bands'].apply(convert)
df['Answer Expected']
&&