— CAN YOU SOLVE THIS - EXCEL CHALLENGE 185 —
(Solutions in any language are also welcome for Excel Challenges)
For the given range in From & To, find the count, smallest and largest numbers where first digit of a number = sum of remaining digits
For ex. 541 where first digit 5 is equal to sum of remaining digits 4 & 1.
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/dp_TuQ4i
#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r
Excel BI’s LinkedIn Post
Adjunto mi query…
La ultima fila demora bastante, a pesar de utilizar buffer, alguna sugerencia??
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Lista = Table.AddColumn(Source, "Custom", each
[a = Record.ToList(_),
b = List.Transform({a{0}..a{1}}, each List.Buffer(List.Transform(Text.ToList(Text.From(_)), Number.From))),
c = List.Transform(b, List.Count),
d = List.Select(b, each List.First(_) = List.Sum(List.Skip(_))),
e = List.Count(d),
f = Number.From(Text.Combine(List.Transform(List.First(d), each Text.From(_)), "")),
g = Number.From(Text.Combine(List.Transform(List.Last(d), each Text.From(_)), ""))
][[e],[f],[g]])[[Custom]],
Sol = Table.ExpandRecordColumn(Lista, "Custom", {"e", "f", "g"}, {"Count", "Smallest", "Largest"})
in
Sol
hi everyone, my proposal solution only for the firsts 4 ranges:
=REDUCE({"Count"\"Smallest"\"Largest"}, MAP(A2:A5, B2:B5, LAMBDA(a, b, LET(_s, SEQUENCE(b-a+1, ,a), _f, FILTER(_s, MAP(_s, LAMBDA(c, LET(_t, TOCOL(--MID(c, SEQUENCE(10),1),3), IFERROR(TAKE(_t, 1)/SUM(DROP(_t, 1)), ))))=1), TEXTJOIN(", ", , COUNT(_f), TAKE(_f, 1), TAKE(_f,-1))))), LAMBDA(i, x, VSTACK(i, TEXTSPLIT(x, ", "))))
I can’t get this one. It’s too hard for my puny brain even to convert into a single array solution (#CALC! error).
This equation only works up until row 5 due to SEQUENCE limitations:
=LET(A,A2, B,B2,
C, SEQUENCE(B-A+1,,A),
D, IFERROR(--MID(C,SEQUENCE(,LEN(B)),1),0),
E, DROP(D,,1),
F, FILTER(C,TAKE(D,,1)=BYROW(E,LAMBDA(E,SUM(E)))),
G, HSTACK(COUNT(F),MIN(F),MAX(F)),
G)
Hi to all!
Slow, but works for all the entries:
=DROP(REDUCE(0,SEQUENCE(ROWS(A2:B6)),LAMBDA(a,v,LET(f,INDEX(A2:A6,v),s,SEQUENCE((9+INDEX(B2:B6,v)-f)/9,9,f),n,TOCOL(s/MAP(s,LAMBDA(x,-LEFT(x)=SUM(-MID(x*10^6,ROW(2:9),1)))),2),VSTACK(a,HSTACK(ROWS(n),MIN(n),MAX(n)))))),1)
Blessings!
First Digit is Equal to Sum of Remaining Digits w/ #powerquery. #bitanbit #powerbi
let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = Table.FromRecords(Table.TransformRows(Source, each
let
r = List.Buffer([List.Select](http://list.select/)({[From]..[To]}, each
let
l = List.Transform(Text.ToList(Text.From(_)), (n) => Number.From(n))
in
l{0} = List.Sum(List.Skip(l))
))
in [Count = List.Count(r), Smallest = List.Min(r), Largest = List.Max(r)]
))
in
Solution
I had the same problem as Sunny Baggu (could not process the last range); however, this formula works for the first 4…
=TEXTSPLIT(TEXTJOIN("/",,IFERROR(MAP(A2:A6,B2:B6,LAMBDA(m,n,LET(s,SEQUENCE(n-m+1,,m),f,FILTER(s,MAP(s,LAMBDA(x,0+LEFT(x)=SUM(0+MID(x,SEQUENCE(,LEN(x)),1))-LEFT(x)))),COUNT(f)&" "&TAKE(f,1)&" "&TAKE(f,-1)))),""))," ","/")
Doesn’t work for the last row (seems like Sequence doesn’t like big numbers) but I tried:
=LET(_nombres;SEQUENCE(B2-A2+1;1;A2);_firstDigit;BYROW(_nombres;LAMBDA(a;GAUCHE(a;1)+0));_sommeRemaining;BYROW(CTXT(_nombres;0);LAMBDA(a;SOMME(SIERREUR(STXT(a;SEQUENCE(1;10;2);1)+0;0))));_nombresQuiMarchent;FILTRE(_nombres;_firstDigit=_sommeRemaining);_resultatsConcat;NBVAL(_nombresQuiMarchent)&";"&MIN(_nombresQuiMarchent)&";"&MAX(_nombresQuiMarchent);FRACTIONNER.TEXTE(_resultatsConcat;";"))
Slow brute force solution:
let
Source = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Table1"]}[Content],{{"From", Int64.Type}, {"To", Int64.Type}}),
Range = List.Buffer({1..List.Max(Source[To])}),
Filtered = List.Buffer([List.Select](http://list.select/)(Range, each
let
digits = Number.RoundDown(Number.Log10(_)),
first = Number.IntegerDivide(_, Number.Power(10, digits)),
rest = Number.Mod(_, Number.Power(10, digits)),
summed = List.Sum(
List.Transform(
{0..digits},
(i) => (
Number.Mod(rest, Number.Power(10, i+1)) -
Number.Mod(rest, Number.Power(10, i))
) / Number.Power(10, i)
)
),
result = (first = summed)
in
result
)),
Result = Table.AddColumn(Source, "Record", each
let in_range = List.Buffer([List.Select](http://list.select/)(Filtered, (i) => i >= [From] and i <= [To]))
in [Count = List.Count(in_range), Smallest = List.Min(in_range), Largest = List.Max(in_range)]
),
Expand = Table.ExpandRecordColumn(Result, "Record", {"Count", "Smallest", "Largest"}, {"Count", "Smallest", "Largest"})
in
Expand
Hii Vijay Sir,
could get this till row 5 due to sequence limitation, (looking forward to learn from others responses)
For now please see my submission:
=DROP(REDUCE("",SEQUENCE(ROWS(A2:B5)),LAMBDA(a,v,VSTACK(a,
LET(_num,SEQUENCE(INDEX(A2:B5,v,2)-INDEX(A2:B5,v,1)+1,,INDEX(A2:B5,v,1)),_left,LEFT(_num)+0,_right,RIGHT(_num,LEN(_num)-1),_rightsum,MAP(_right,LAMBDA(a,SUM(MID(a,SEQUENCE(LEN(a)),1)+0))),_cond,(_left=_rightsum)+0,
_cnt,SUM(_cond),_smallest,XLOOKUP(1,_cond,_num),_largest,XLOOKUP(1,_cond,_num,,,-1),HSTACK(_cnt,_smallest,_largest))))),1)
=REDUCE({"Count","Smallest","Largest"},MAP(A2:A6,B2:B6,LAMBDA(x,y,LET(m,SEQUENCE(1000000,10),n,--(IFERROR(MAP(m,LAMBDA(x,SUM(--MID(x,SEQUENCE(LEN(x)-1,,2),1)))),0)=--LEFT(m)),o,TOCOL(IF(n=1,m,m/0),3),p,(o>=x)*(o<=y)*o,q,IF(p=0,"",p),TEXTJOIN("-",1,COUNT(q),MIN(q),MAX(q))))),LAMBDA(a,b,VSTACK(a,--TEXTSPLIT(b,"-"))))
my solution for the range A2:B5
=LET(_d,A2:B5,fx,LAMBDA(r,LET(_n1,INDEX(r,1,1),_n2,INDEX(r,1,2),_s,SEQUENCE(_n2-_n1,,_n1),_m,MAP(--LEFT(_s),--RIGHT(_s,LEN(_s)-1),LAMBDA(a,b,a=SUM(--MID(b,SEQUENCE(LEN(b)),1)))),HSTACK(SUM(--_m),XLOOKUP(TRUE,_m,_s),XLOOKUP(TRUE,_m,_s,,,-1)))),REDUCE(fx(TAKE(_d,1)),DROP(SEQUENCE(ROWS(_d)),1),LAMBDA(a,b,VSTACK(a,fx(CHOOSEROWS(_d,b))))))
