Excel BI Challenge Workout 189

[EnterpriseDNA]

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

Populate the count of numbers larger than the number given in a cell. The range to be considered is not entire column but starting that cell till the end. Hence, in case of A4, the you need to find the count of numbers > A4 in the range A4:A20

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/daFTUKga

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

Excel BI’s LinkedIn post:

Excel BI on LinkedIn: #excel #excelchallenge #powerquerychallenge...

[EnterpriseDNA]

LinkedIn Post by:
Zoran Milokanović

Counting of elements w/ #powerquery. #bitanbit #powerbi

let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution =
let
Numbers = List.Accumulate(Source[Numbers], {}, (s, c) => s & {{(List.Last(s){0}? ?? -1) + 1, c}})
in
List.Transform(Numbers, (l) => List.Count([List.Select](http://list.select/)(List.Skip(Numbers, l{0}), each _{1} > l{1})))
in
Solution

[EnterpriseDNA]

LinkedIn Post by:
Kim van Gerwen

Coded in Javascript:

const numbers = [17, 1, 16, 17, 8, 15, 11, 2, 11, 5, 14, 16, 17, 15, 12, 19, 4, 18, 13];

function countLargerThan(array) {
  let newArray = [];
  for (let i = 0; i < array.length; i++) {
    let count = 0;
    for (let j = i; j < array.length; j++) {
      if (array[j] > array[i]) {
        count++;
      }
    }
    newArray.push(count);
  }
  return newArray;
}

console.log(countLargerThan(numbers));

[EnterpriseDNA]

LinkedIn post by:
Alejandro Simón

Adjunto mi query…

No me dan los mismos resultados que en la imagen. Sin embargo, el resultado es igual al de Bo Rydobon y, por supuesto, con una query no tan sofisticada como la de Bo.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Index = Table.AddIndexColumn(Source, "Idx"),
Sol = Table.AddColumn(Index, "Answer", (x)=>
let
a = Source[Numbers],
b = List.LastN(a, List.Max(a) - Index[Idx]{x[Idx]}),
c = [List.Select](http://list.select/)(b, each _ > Index[Numbers]{x[Idx]})
in List.Count(c))[[Answer]]
in
Sol

[EnterpriseDNA]

LinkedIn post by:
Sunny Baggu

=LET(
    _num, A2:A20,
    _seq, SEQUENCE(ROWS(_num)),
    MAP(_seq, LAMBDA(a, SUM(N(DROP(_num, a - 1) > CHOOSEROWS(_num, a)))))
)

with the same mismatches of others

[EnterpriseDNA]

LinkedIn Post by:
Omid Motamedisedeh

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  #"Added Index" = [A=Table.AddIndexColumn(Source, "Index", 0, 1), B=Table.RemoveColumns(Table.AddColumn(A, "anwser", (ox)=> Table.RowCount(Table.SelectRows(A, each _[Numbers]>ox[Numbers] and _[Index]>ox[Index]))),"Index")][B]
in
  #"Added Index"

[EnterpriseDNA]

LinkedIn Post by:
Julien Lacaze

Mind blower solution : (pick only restrictive array and compare)

=MAP(A2:A20,SEQUENCE(ROWS(A2:A20)),LAMBDA(a,b,REDUCE(0,DROP(A2:A20,b-1),LAMBDA(ac,v,IF(v>a,ac+1,ac)))))

But, a simple =COUNTIFS(A2:$A$20,“>”&A2) then pasted down works.

[EnterpriseDNA]

LinkedIn Post by:
Stevenson Yu

I suppose I can use my brain a bit and come up with a single formula using MAP, REDUCE, and what have you. But you know what, it can be done with a simple COUNTIF, so that’s what I’m going with since my brain is not big enough😅

=COUNTIF(A2:A$20,">"&A2)

To be placed in B2 and then copy/pasted down.

[EnterpriseDNA]

LinkedIn Post by:
Bo Rydobon

M-Code

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Count = List.Transform({1..Table.RowCount(Source)}, (n)=> List.Count([List.Select](http://list.select/)(List.Skip(Source[Numbers],n),each _> Source[Numbers]{n-1})))
in
Count

[EnterpriseDNA]

LinkedIn post by:
محمد حلمي

=MAP(A2:A17,LAMBDA(a,SUM(--(a:A17>a))))

[EnterpriseDNA]

LinkedIn Post by
Guillermo Arroyo

Power Query

let
Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
a = List.Transform({0..List.Count(Origen[Numbers])-1}, (x) => List.Count([List.Select](http://list.select/)(List.Alternate(Origen[Numbers], x), each _ > Origen[Numbers]{x})))
in
a

[EnterpriseDNA]

LinkedIn Post by:
Hussain Ali Nasser

=LET(_fixedrange,A2:A20,MAP(_fixedrange,SEQUENCE(ROWS(_fixedrange)),LAMBDA(_range,_iterations,LET(_drop,DROP(_fixedrange,_iterations),_filter,FILTER(_drop,_drop>_range),COUNT(_filter)))))

[EnterpriseDNA]

LinkedIn post by:
Kris Jaganah

=LET(a,A2:A20,MAP(a,SEQUENCE(ROWS(a),,0),LAMBDA(x,y,COUNT(FILTER(DROP(a,y),DROP(a,y)>x)))))

[EnterpriseDNA]

LinkedIn post by:
Luan Rodrigues

let
 Fonte = Tabela1,
 Ind = Table.AddIndexColumn(Fonte, "Ind", 1, 1, Int64.Type),
 res = Table.AddColumn(Ind, "Personalizar", each 
 List.Count(List.Select(List.RemoveFirstN(Ind[Numbers],[Ind]), (x)=> x > [Numbers])))
 [[Numbers],[Personalizar]]
in
 res

[EnterpriseDNA]

LinkedIn Post by:
John Jairo V.

Hi to all!

Two options:
=MAP(A2:A20,LAMBDA(x,COUNTIF(x:A20,">"&x)))
=MAP(A2:A20,LAMBDA(x,SUM(N(x:A20>x))))

Blessings!

[EnterpriseDNA]

LinkedIn post by:
Rick Rothstein

=MAP(A2:A20,LAMBDA(a,SUM(0+(a:A20>a))))

EDIT NOTE: I get the same mismatches as Bo Rydobon did.

[EnterpriseDNA]

LnkedIn Post by:
Bo Rydobon

I got mismatch on B2 18>17 , B9 12>11 and B18 3>2
Not sure how to count that

=MAP(A2:A20,LAMBDA(a,COUNTIF(a:A20,">"&a)))

[EnterpriseDNA]

LinkedIn Post by:
Oscar Mendez Roca Farell

hi everyone, my proposal solution:

=MAP(A2:A20, LAMBDA(a, COUNTIF(DROP(A2:A20, ROWS(A2:a)-1), ">"&a)))

[EnterpriseDNA]

LinkedIn post by:
Paolo Pozzoli

Quick and easy, but longer than others

[EnterpriseDNA]

LinkedIn Post by:
Nicolas Micot

=[NB.SI](http://nb.si/)($A2:$A$20;">"&A2)