# Excel BI Challenge Workout 189

— 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

(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

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

## 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));
``````

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],
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
Sol
``````

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

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
in
``````

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.

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.

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

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

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

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

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

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

## 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!

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

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

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

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

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