Home » Count Larger Numbers Below

Count Larger Numbers Below

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

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 189
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Count Larger Numbers Below with Power Query

Power Query solution 1 for Count Larger Numbers Below, proposed 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"
Power Query solution 2 for Count Larger Numbers Below, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Count = List.Transform(
    {1 .. Table.RowCount(Source)}, 
    (n) => List.Count(List.Select(List.Skip(Source[Numbers], n), each _ > Source[Numbers]{n - 1}))
  )
in
  Count
Power Query solution 3 for Count Larger Numbers Below, proposed by Zoran Milokanović: 
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(List.Skip(Numbers, l{0}), each _{1} > l{1}))
      )
in
  Solution
Power Query solution 4 for Count Larger Numbers Below, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol = List.Transform(
    {0 .. List.Count(Source[Numbers]) - 1}, 
    each 
      let
        a = List.Skip(Source[Numbers], _), 
        b = Source[Numbers]{_}, 
        c = List.Difference(a, {b}), 
        d = List.Count(List.Select(c, each _ > b))
      in
        d
  )
in
  Sol
Power Query solution 5 for Count Larger Numbers Below, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
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(b, each _ > Index[Numbers]{x[Idx]})
      in
        List.Count(c)
  )[[Answer]]
in
  Sol
Power Query solution 6 for Count Larger Numbers Below, proposed 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
Power Query solution 7 for Count Larger Numbers Below, proposed by Guillermo Arroyo: 
let
  Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content], 
  a = List.Transform(
    {0 .. List.Count(Origen[Numbers]) - 1}, 
    (x) => List.Count(List.Select(List.Alternate(Origen[Numbers], x), each _ > Origen[Numbers]{x}))
  )
in
  a

Solving the challenge of Count Larger Numbers Below with Excel

Excel solution 1 for Count Larger Numbers Below, proposed by Bo Rydobon 🇹🇭: 
=MAP(A2:A20,LAMBDA(a,COUNTIF(a:A20,">"&a)))
Excel solution 2 for Count Larger Numbers Below, proposed by Rick Rothstein: 
=MAP(A2:A20,LAMBDA(a,SUM(0+(a:A20>a))))
Excel solution 3 for Count Larger Numbers Below, proposed by John V.: 
=MAP(A2:A20,LAMBDA(x,COUNTIF(x:A20,">"&x)))
✅=MAP(A2:A20,LAMBDA(x,SUM(N(x:A20>x))))
Excel solution 4 for Count Larger Numbers Below, proposed by محمد حلمي: 
=MAP(A2:A17,LAMBDA(a,SUM(--(a:A17>a))))
Excel solution 5 for Count Larger Numbers Below, proposed 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)))))
Excel solution 6 for Count Larger Numbers Below, proposed by Julian Poeltl: 
=LET(N,A2:A20,MAP(N,SEQUENCE(ROWS(N)),LAMBDA(A,B,IFERROR(ROWS(FILTER(DROP(N,B-1),DROP(N,B-1)>A)),0))))
Excel solution 7 for Count Larger Numbers Below, proposed by Timothée BLIOT: 
=LET(A,A2:A20,MAP(SEQUENCE(ROWS(A),,0),LAMBDA(x,COUNTIFS(DROP(A,x),">"&INDEX(A,x+1)))))
Excel solution 8 for Count Larger Numbers Below, proposed by Hussein SATOUR: 
=LET(n,A2:A20,MAP(n,LAMBDA(x,COUNTIF(x:TAKE(n,-1),">"&x))))
Excel solution 9 for Count Larger Numbers Below, proposed by Oscar Mendez Roca Farell: 
=MAP(A2:A20, LAMBDA(a, COUNTIF(DROP(A2:A20, ROWS(A2:a)-1), ">"&a)))
Excel solution 10 for Count Larger Numbers Below, proposed by Duy Tùng: 
=REDUCE("Answer",SEQUENCE(ROWS(A2:A20)),LAMBDA(x,y,LET(a,OFFSET(A1,y,,19+1-y),VSTACK(x,COUNT(FILTER(a,a>INDEX(A2:A20,y)))))))
Excel solution 11 for Count Larger Numbers Below, proposed by Sunny Baggu: 
=LET(
 _num, A2:A20,
 _seq, SEQUENCE(ROWS(_num)),
 MAP(_seq, LAMBDA(a, SUM(N(DROP(_num, a - 1) > CHOOSEROWS(_num, a)))))
)
Excel solution 12 for Count Larger Numbers Below, proposed by Md. Zohurul Islam: 
=LET(z,A2:A20,n,ROWS(z),sq,SEQUENCE(n),
w,MAP(sq,LAMBDA(x,LET(
a,INDEX(z,x),
b,TAKE(z,-(n-x+1)),
d,COUNT(FILTER(b,b>a,"Na")),
d))),
w)
Excel solution 13 for Count Larger Numbers Below, proposed by Charles Roldan: 
=LET(
 Numbers, A2:A20,
 Indices, SEQUENCE(ROWS(Numbers)),
 f, LAMBDA(x, x < TRANSPOSE(x)),
 BYROW(f(Numbers) * f(Indices), LAMBDA(x, SUM(x))) )
Excel solution 14 for Count Larger Numbers Below, proposed by Julien Lacaze: 
=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)
Excel solution 15 for Count Larger Numbers Below, proposed by Nicolas Micot: 
=NB.SI($A2:$A$20;">"&A2)
Excel solution 16 for Count Larger Numbers Below, proposed by Guillermo Arroyo: 
=MAP(A2:A20,LAMBDA(a,SUM(--(a:A20>a))))
Excel solution 17 for Count Larger Numbers Below, proposed 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)))))
Excel solution 18 for Count Larger Numbers Below, proposed by Stevenson Yu: 
=COUNTIF(A2:A$20,">"&A2)
Excel solution 19 for Count Larger Numbers Below, proposed by Abdul Said M K.: 
=COUNTIF($A$2:$A$20,">" & A2)
Excel solution 20 for Count Larger Numbers Below, proposed by Caroline Blake: 
=BYROW(A2:A20,
    LAMBDA(x,
    SUM(--(x:A20>x))))
Excel solution 21 for Count Larger Numbers Below, proposed by Liz Larsen, P.E.: 
=BYROW(A2:A20,LAMBDA(x,COUNTIF(x:A20,">"&x)))

Solving the challenge of Count Larger Numbers Below with Python in Excel

Python in Excel solution 1 for Count Larger Numbers Below, proposed by Alejandro Campos: 
df = xl("A1:A20", headers=True)
def calculate_counts(numbers):
 counts = []
 for i in range(len(numbers)):
 count = sum(num > numbers[i] for num in numbers[i+1:])
 counts.append(count)
 return counts
df['Result'] = calculate_counts(df['Numbers'].tolist())
df['Result']

&&&

Leave a Reply