List the operator which operates between Number 1 and Number 2. It is Number 1 Operator Number 2 not Number 2 Operator Number 1 In case of multiple operators, separate them by commas.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 198
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Detect Operators Between Numbers with Power Query
Power Query solution 1 for Detect Operators Between Numbers, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.TransformRows(
Source,
each
let
a = [Number1],
b = [Number2],
c = [Result]
in
Text.Combine(
{
if a + b = c then "+" else null,
if a - b = c then "-" else null,
if a * b = c then "*" else null,
if a / b = c then "/" else null
},
", "
)
)
in
Ans
Power Query solution 2 for Detect Operators Between Numbers, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
Solution = Table.TransformRows(
Source,
(o) =>
Text.Combine(
List.Select(
{"+", "-", "*", "/"},
each Expression.Evaluate(
Text.From(o[Number1]) & _ & Text.From(o[Number2]) & "=" & Text.From(o[Result])
)
),
", "
)
)
in
Solution
Power Query solution 3 for Detect Operators Between Numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.ToRows(Source),
Sol = List.Transform(
a,
each Text.Combine(
{if _{0} + _{1} = _{2} then "+" else null}
& {if _{0} - _{1} = _{2} then "-" else null}
& {if _{0} * _{1} = _{2} then "*" else null}
& {if _{0} / _{1} = _{2} then "/" else null},
", "
)
)
in
Sol
Power Query solution 4 for Detect Operators Between Numbers, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each Text.Combine(
Table.SelectRows(
Table.FromValue(
[
a1 = [Number1],
a2 = [Number2],
a3 = [Result],
a = if a1 + a2 = a3 then "+" else null,
b = if a1 - a2 = a3 then "-" else null,
c = if a1 * a2 = a3 then "*" else null,
d = if a1 / a2 = a3 then "/" else null
]
),
each [Value] is text
)[Value],
", "
)
)
in
res
Power Query solution 5 for Detect Operators Between Numbers, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Answer = Table.TransformRows(
Source,
each Text.Combine(
List.Select(
{"+", "-", "*", "/"},
(a) => [Result] = Expression.Evaluate(Text.From([Number1]) & a & Text.From([Number2]))
),
","
)
)
in
Answer
Power Query solution 6 for Detect Operators Between Numbers, proposed by Guillermo Arroyo:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
a = {"+", "-", "*", "/"},
b = List.Transform(
List.Zip({Origen[Number1], Origen[Number2]}),
each {_{0} + _{1}, _{0} - _{1}, _{0} * _{1}, _{0} / _{1}}
),
c = List.Transform(List.Zip({Origen[Result], b}), (x) => List.Transform(x{1}, each _ = x{0})),
d = List.Transform(c, (y) => Text.Combine(List.Select(a, each y{List.PositionOf(a, _)}), ", "))
in
d
Solving the challenge of Detect Operators Between Numbers with Excel
Excel solution 1 for Detect Operators Between Numbers, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,B2:B10,C2:C10,LAMBDA(a,b,c,TEXTJOIN(", ",,REPT({"+","-","*","/"},HSTACK(a+b,a-b,a*b,IF(b,a/b))=c))))
Excel solution 2 for Detect Operators Between Numbers, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,B2:B10,C2:C10,LAMBDA(a,b,c,ARRAYTOTEXT(FILTER({"+","-","*","/"},HSTACK(a+b,a-b,a*b,IF(b,a/b))=c))))
Excel solution 3 for Detect Operators Between Numbers, proposed by Rick Rothstein:
=LET(a,A2:A10,b,B2:B10,c,C2:C10,SUBSTITUTE(TRIM(IF(a+b=c,"+ ","")&IF(ABS(a-b)=c,"- ","")&IF(a*b=c,"* ","")&IF(IFERROR(a/b=c,0)+IFERROR(b/a=c,0),"/ ",""))," ",", "))
Excel solution 4 for Detect Operators Between Numbers, proposed by John V.:
=MAP(A2:A10,B2:B10,C2:C10,LAMBDA(a,b,c,TEXTJOIN(", ",,REPT({"+","-","*","/"},HSTACK(a+b,a-b,a*b,IF(b,a/b))=c))))
Define name g: =LAMBDA(x,EVALUATE(x)) and then use:
✅=LET(o,{"+","-","*","/"},BYROW(REPT(o,IFERROR(g(A2:A10&o&B2:B10)=C2:C10,)),LAMBDA(x,TEXTJOIN(", ",,x))))
Excel solution 5 for Detect Operators Between Numbers, proposed by محمد حلمي:
=LET(a,A2:A10,b,B2:B10,BYROW(IFERROR(HSTACK(a+b,a-b,a*b,a/b)=C2:C10,),LAMBDA(a,TEXTJOIN(", ",,REPT({"+","-","*","/"},a)))))
Excel solution 6 for Detect Operators Between Numbers, proposed by Kris Jaganah:
=LET(a,A2:A10,b,B2:B10,c,C2:C10,d,{1,2,3,4},BYROW(XLOOKUP(HSTACK(a+b=c,a-b=c,a*b=c,IFERROR(a/b=c,0))*d,d,{"+","-","*","/"},""),LAMBDA(x,TEXTJOIN(", ",1,x))))
Excel solution 7 for Detect Operators Between Numbers, proposed by Julian Poeltl:
=MAP(A2:A10,B2:B10,C2:C10,LAMBDA(O,T,R,TEXTJOIN(", ",,FILTER(HSTACK("+","-","*","/"),HSTACK(O+T=R,O-T=R,O*T=R,IFERROR(O/T=R,0))))))
Excel solution 8 for Detect Operators Between Numbers, proposed by Timothée BLIOT:
=MAP(A2:A10,B2:B10,C2:C10,LAMBDA(a,b,c,TEXTJOIN(",",1,IF(c=a+b,"+",""),IF(c=a-b,"-",""),IF(c=a*b,"*",""),IFERROR(IF(c=a/b,"/",""),""))))
Excel solution 9 for Detect Operators Between Numbers, proposed by Hussein SATOUR:
=LET(a, A2:A10, b, B2:B10, BYROW(IFERROR(HSTACK(a+b, a-b, a*b, a/b)=C2:C10,0), LAMBDA(x, TEXTJOIN(", ",, FILTER({"+","-","*","/"},x)))))
Excel solution 10 for Detect Operators Between Numbers, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A10, B2:B10, C2:C10, LAMBDA(a, b, c, LET(_f, LAMBDA(i, j, APILARV(i+j, i-j ,i*j, IFERROR(i/j, ""))), TEXTJOIN(", ", , FILTER({"+","-","*","/"},_f(a, b)=c)))))
Excel solution 11 for Detect Operators Between Numbers, proposed by Sunny Baggu:
=MAP(A2:A10,B2:B10,C2:C10,LAMBDA(a,b,c,TEXTJOIN(", ",,
IFERROR(IF(a+b=c,"+",""),""),
IFERROR(IF(a-b=c,"-",""),""),
IFERROR(IF(a*b=c,"*",""),""),
IFERROR(IF(a/b=c,"/",""),""))))
Excel solution 12 for Detect Operators Between Numbers, proposed by Sunny Baggu:
=MAP(
A2:A10,
B2:B10,
C2:C10,
LAMBDA(a, b, c,
ARRAYTOTEXT(
FILTER(
{"+"; "-"; "*"; "/"},
VSTACK((a + b = c) + 0, (a - b = c) + 0, (a * b = c) + 0, IFERROR(a / b = c, 0))
)
)
)
)
Excel solution 13 for Detect Operators Between Numbers, proposed by Sunny Baggu:
=MAP(
A2:A10,
B2:B10,
C2:C10,
LAMBDA(a, b, c,
TEXTJOIN(
", ",
,
XLOOKUP(
VSTACK(
IF(a + b = c, 1, 0),
IF(a - b = c, 2, 0),
IF(a * b = c, 3, 0),
IFERROR(IF(a / b = c, 4), 0)
),
{1; 2; 3; 4},
{"+"; "-"; "*"; "/"},
""
)
)
)
)
Excel solution 14 for Detect Operators Between Numbers, proposed by LEONARD OCHEA 🇷🇴:
=BYROW(LET(a,A2:A10,b,B2:B10,c,C2:C10,IFERROR(CHOOSE(HSTACK((a+b=c)*1,(a-b=c)*2,(a*b=c)*3,(a/b=c)*4),"+","-","*","/"),"")),LAMBDA(x,TEXTJOIN(", ",,x)))
Excel solution 15 for Detect Operators Between Numbers, proposed by Md. Zohurul Islam:
=LET(u,A2:A10,v,B2:B10,w,C2:C10,z,{"+","-","*","/"},
a,IFERROR(HSTACK(u+v,u-v,u*v,u/v),""),
b,BYROW(IF(a=w,z,""),LAMBDA(x,TEXTJOIN(", ",1,x))),
b)
Excel solution 16 for Detect Operators Between Numbers, proposed by JvdV –:
=LET(s,{"+","-","*","/"},BYROW(REPT(s,IFERROR(z(A2:A10&s&B2:B10)=C2:C10,)),LAMBDA(r,TEXTJOIN(",",,r))))
Where z() is a named function refering to:
=LAMBDA(x,EVALUATE(x))
Excel solution 17 for Detect Operators Between Numbers, proposed by Julien Lacaze:
=LET(_n1,A2:A10,_n2,B2:B10,_r,C2:C10,
_add,--((_n1+_n2)=_r),
_sub,--((_n1-_n2)=_r),
_mul,--((_n1*_n2)=_r),
_div,--(IFERROR(_n1/_n2,"")=_r),
MAP(_add,_sub,_mul,_div,LAMBDA(a,s,m,d,TEXTJOIN(", ",TRUE,IF(a,"+",""),IF(s,"-",""),IF(m,"*",""),IF(d,"/",""))
)))
Excel solution 18 for Detect Operators Between Numbers, proposed by Guillermo Arroyo:
=MAP(A2:A10,B2:B10,C2:C10,LAMBDA(a,b,c,TEXTJOIN(", ",1,FILTER({"+";"-";"*";"/"},VSTACK(a+b,a-b,a*b,IFERROR(a/b,""))=c,""))))
Excel solution 19 for Detect Operators Between Numbers, proposed by Daniel Garzia:
=MAP(A2:A10,B2:B10,C2:C10,LAMBDA(a,b,r,TEXTJOIN(", ",,IF(VSTACK(a+b,a-b,a*b,IFERROR(a/b,""))=r,{"+";"-";"*";"/"},""))))
Excel solution 20 for Detect Operators Between Numbers, proposed by Rayan S.:
=LET(x, A2:A10, y, B2:B10, z, C2:C10, sol, IFERROR(HSTACK(IF(x *y = z, "*", ""), IF(x + y = z, "+", ""), IF(x - y = z, "-", ""), IF(x / y = z, "/", "")), ""), a, TAKE(sol, , 1), b, TAKE(sol, , -1), c, DROP(TAKE(sol, , 2), , 1), d, DROP(TAKE(sol, , -2), , -1), MAP(a,b,c,d,LAMBDA(a,b,c,d,TEXTJOIN(", ",,a,c,d,b))))
Excel solution 21 for Detect Operators Between Numbers, proposed by Juliano Santos Lima:
=CHOOSE(MATCH(C2,CHOOSE({1,2,3,4},A2+B2,A2-B2,A2*B2,A2/B2),0),"+","-","*","/")
Another version...
=CHOOSE(MATCH(C2,HSTACK(A2+B2,A2-B2,A2*B2,A2/B2),0),"+","-","*","/")
Excel solution 22 for Detect Operators Between Numbers, proposed by Miguel Angel Franco García:
=LET(a; APILARH("*";" /";" +";" -");b;SÍ. ERROR(A2*B2,""); c;SÍ. ERROR(A2/B2,""); d;SÍ. ERROR(A2+B2,""); e;SÍ. ERROR(A2-B2,""); UNIRCADENAS(","; VERDADERO;SI(APILARH(b;c;d;e)=C2,a;"")))
Excel solution 23 for Detect Operators Between Numbers, proposed by Stevenson Yu:
=MAP(A2:A10,B2:B10,C2:C10, LAMBDA(X,Y,Z,
LET(A, X, B, Y, C, Z,
D, IF(C=A+B,"+",""),
E, IF(C=A-B,"-",""),
F, IF(C=A*B,"*",""),
G, IFERROR(IF(C=A/B,"/",""),""),
TEXTJOIN(", ",1,D,E,F,G))))
Excel solution 24 for Detect Operators Between Numbers, proposed by Surendra Reddy:
=LET(a,{"+";"-";"*";"/"},MAP(A2:A10,B2:B10,C2:C10,LAMBDA(x,y,z,TEXTJOIN(", ",,FILTER(a,VSTACK(x+y,x-y,x*y,IFERROR(x/y,""))=z)))))
Excel solution 25 for Detect Operators Between Numbers, proposed by Surendra Reddy:
=MAP(A2:A10,B2:B10,C2:C10,LAMBDA(x,y,z,TEXTJOIN(", ",,IF(x+y=z,"+",""),IF(x-y=z,"-",""),IF(x*y=z,"*",""),IF((IFERROR(x/y,"")=z),"/",""))
))
Excel solution 26 for Detect Operators Between Numbers, proposed by Surendra Reddy:
=LET(a,{"+","-","*","/"},MAP(A2:A10,B2:B10,C2:C10,LAMBDA(x,y,z,TEXTJOIN(", ",,FILTER(a,HSTACK(x+y,x-y,x*y,IFERROR(x/y,""))=z)))))
Solving the challenge of Detect Operators Between Numbers with Python in Excel
Python in Excel solution 1 for Detect Operators Between Numbers, proposed by Alejandro Campos:
df = xl("A1:C10", headers=True)
def find_operator(row):
n1, n2, result = row['Number1'], row['Number2'], row['Result']
operators = []
if n1 + n2 == result:
operators.append('+')
if n1 - n2 == result:
operators.append('-')
if n1 * n2 == result:
operators.append('*')
if n2 != 0 and n1 / n2 == result:
operators.append('/')
return ', '.join(operators)
df['Operator'] = df.apply(find_operator, axis=1)
df
&&&
