# Excel BI Challenge Workout 198

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 198 —
(Solutions in any language are also welcome for Excel Challenges)

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.

(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)

#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r

## LinkedIn Post by:Miguel Angel Franco García

Soluccion

`=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;"")))`

Fun alternative:
``````=LET(s,{"+","-","*","/"},BYROW(REPT(s,IFERROR(z(A2:A10&s&B2:B10)=C2:C10,)),LAMBDA(r,TEXTJOIN(",",,r))))

Where z() is a named function referring to:

=LAMBDA(x,EVALUATE(x))
``````

`=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))))`

``````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
``````

I did not assume that for subtraction and division, that the cell order would always be large number in Column A, small number in Column B (for example, my formula works if the values in cells A5 and B5 are reversed or if the values in cells A8 and B8 are reversed)…

``````=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),"/ ",""))," ",", "))
``````

EDIT NOTE: My formula does assume all values are positive for subtraction.

## LinkedIn Post by:John Jairo V.

Hi to all! Two options:

`=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))))`

Blessings!

``````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
``````

``````=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))))
``````

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,"/",""),""))))
``````

Power Query
``````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
``````

whit BYROW + CHOOSE

` =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)))`

`=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))))`

``````=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},
{"+"; "-"; "*"; "/"},
""
)
)
)
)
``````

To Avoid Multiple IF in the formula posted earlier.

`=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)))))`

M-Code
``````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
``````

``````=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))
)
)
)
)
``````

`=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)))))`

## LinkedIn Post by:Oscar Mendez Roca Farell

hi everyone, my proposal solution:

`=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)))))`

``````let