Excel BI Challenge Workout 198

Workouts Excel BI Challenge
1

— 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.

Post answers in Comment.

(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)

Download Practice File - https://lnkd.in/dn2XGEdU

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

image
image800×491 89.9 KB

LinkedIn Post

2

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

3

LinkedIn Post by:
JvdV

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))
4

LinkedIn Post by:
Muhammad 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))))

5

LinkedIn Post by:
Alejandro Simón

Adjunto mi query... 
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
6

LinkedIn Post by:
Rick Rothstein

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.

7

LinkedIn Post by:
John Jairo V.

Hi to all!

:white_check_mark:

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

:white_check_mark:
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!

8

LinkedIn Post 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
9

LinkedIn Post 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))))
10

LinkedIn Post 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,"/",""),""))))
11

LinkedIn Post by:
Guillermo Arroyo

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

image
image745×121 28.6 KB

12

LinkedIn Post by:
LEONARD OCHEA

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

13

LinkedIn Post 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))))

14

LinkedIn Post 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},
 {"+"; "-"; "*"; "/"},
 ""
 )
 )
 )
)
15

LinkedIn Post by:
Surendra Reddy

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

16

Linkedin Post by:
Bo Rydobon

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
17

LinkedIn Post 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))
 )
 )
 )
)
18

LinkedIn Post by:
Surendra Reddy

Using HSTACK instead of VSTACK

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

19

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

20

Linkedin Post 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