Home » Classify Deficient Perfect Abundant

Classify Deficient Perfect Abundant

Take a positive integer n and add its positive divisors (let’s denote it by S) Classify the numbers as per given criteria: If S < 2n = Deficient Number If S = 2n = Perfect Number If S > 2n = Abundant Number Ex. n = 12, its positive divisors are 1, 2, 3, 4, 6, 12 whose sum is 28. Since S > 2n as 28 > 24, hence this number is Abundant Number.

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

Solving the challenge of Classify Deficient Perfect Abundant with Power Query

Power Query solution 1 for Classify Deficient Perfect Abundant, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ans = Table.AddColumn(
    Source, 
    "Ans", 
    each 
      let
        d = List.Sum(
          List.Distinct(
            List.Combine(
              List.Transform(
                {1 .. Int64.From(Number.Sqrt([Numbers]))}, 
                (m) => if Number.Mod([Numbers], m) = 0 then {m, [Numbers] / m} else {}
              )
            )
          )
        )
          - [Numbers]
          * 2
      in
        if d = 0 then "Perfect" else if d > 0 then "Abundant" else "Deficient"
  )
in
  Ans
Power Query solution 2 for Classify Deficient Perfect Abundant, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  S = Table.TransformRows(
    Source, 
    each 
      let
        S = List.Sum(
          List.Select({1 .. Number.RoundDown([Numbers] / 2)}, (n) => Number.Mod([Numbers], n) = 0)
            & {[Numbers]}
        ), 
        N2 = 2 * [Numbers]
      in
        if S < N2 then "Deficient" else if S = N2 then "Perfect" else "Abundant"
  )
in
  S
Power Query solution 3 for Classify Deficient Perfect Abundant, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  res = Table.AddColumn(
    Fonte, 
    "Personalizar", 
    each [
      a = List.Sum(List.Select({1 .. [Numbers]}, (x) => Number.Mod([Numbers], x) = 0)), 
      b = [Numbers] * 2, 
      c = if a < b then "Deficient" else if a = b then "Perfect" else "Abundant"
    ][c]
  )
in
  res
Power Query solution 4 for Classify Deficient Perfect Abundant, proposed by Rafael González B.: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 ChT = Table.TransformColumnTypes(Source,{{"Numbers", Int64.Type}}),
 Result = Table.AddColumn(ChT, "Answer Expected", each
 let
 n = [Numbers],
 a = {1..[Numbers]},
 b = List.Transform(a, each n/_),
 c = List.Select(b, each   Number.Mod(_,1) = 0  ),
 S = List.Sum(c),
 n2 = n * 2,
 Answ = if S < n2 then "Deficient" else
 if S = n2 then "Perfect" else "Abundant"
 
 in
 )
in
 Result

Solving the challenge of Classify Deficient Perfect Abundant with Excel

Excel solution 1 for Classify Deficient Perfect Abundant, proposed by Bo Rydobon 🇹🇭: 
=MAP(A2:A10,LAMBDA(n,LET(m,SEQUENCE(n),SWITCH(SIGN(SUM(IF(MOD(n,m),,m))-n*2),0,"Perfect",1,"Abundant","Deficient"))))
Excel solution 2 for Classify Deficient Perfect Abundant, proposed by Bo Rydobon 🇹🇭: 
=MAP(A2:A10,LAMBDA(n,LET(m,SEQUENCE(n^0.5),S,SUM(UNIQUE(TOCOL(IFS(MOD(n,m)=0,HSTACK(m,n/m)),3))),SWITCH(SIGN(S-2*n),0,"Perfect",1,"Abundant","Deficient"))))
Excel solution 3 for Classify Deficient Perfect Abundant, proposed by John V.: 
=MAP(A2:A10,LAMBDA(x,LET(s,SEQUENCE(x),CHOOSE(2+SIGN(SUM(s*(MOD(x,s)=0))-2*x),"Deficient","Perfect","Abundant"))))
Excel solution 4 for Classify Deficient Perfect Abundant, proposed by محمد حلمي: 
=MAP(A2:A8,LAMBDA(a,LET(i,SEQUENCE(a),s,SUM(FILTER(i,MOD(a/i,1)=0)),IFS(a*2>s,"Deficient",a*2=s,"Perfect",1,"Abundant"))))
Excel solution 5 for Classify Deficient Perfect Abundant, proposed by محمد حلمي: 
=MAP(A2:A8,LAMBDA(a,LET(i,SEQUENCE(a),s,SUM(IF(MOD(a,i),,i)),IFS(a*2>s,"Deficient",a*2=s,"Perfect",1,"Abundant"))))
Excel solution 6 for Classify Deficient Perfect Abundant, proposed by Julian Poeltl: 
=MAP(A2:A10,LAMBDA(N,LET(S,SEQUENCE(N),C,SUM(FILTER(S,MOD(N/S,1)=0)),IFS(C<2*N,"Deficient",C=2*N,"Perfect",C>2*N,"Abundant"))))
Excel solution 7 for Classify Deficient Perfect Abundant, proposed by Timothée BLIOT: 
=MAP(A2:A10,LAMBDA(x,LET(A,SUM(FILTER(SEQUENCE(x),IF(MOD(x,SEQUENCE(x))=0,1,0))),SWITCH(TRUE,2*xA,"Deficient","na"))))
Excel solution 8 for Classify Deficient Perfect Abundant, proposed by Hussein SATOUR: 
=MAP(A2:A10, LAMBDA(x, LET(a, SEQUENCE(x), b, SUM(FILTER(a, x/a = INT(x/a))), IFS(b<2*x, "Deficient", b>2*x, "Abundant", 1, "Perfect"))))
Excel solution 9 for Classify Deficient Perfect Abundant, proposed by Oscar Mendez Roca Farell: 
=MAP(A2:A10, LAMBDA(a, LET(_s, SEQCUENCE(a), CHOOSE(2+SIGN(2*a/SUM(IF(MOD(a/_s, 1), ,_s))-1), "Abundat", "Perfect", "Deficient"))))
Excel solution 10 for Classify Deficient Perfect Abundant, proposed by Sunny Baggu: 
=MAP(
 A2:A10,
 LAMBDA(_n,
 LET(
 _seq, WRAPROWS(SEQUENCE(_n), 200),
 _s, SUM(TOCOL(IFS(MOD(_n, _seq) = 0, _seq), 3)),
 IFS(_s < 2 * _n, "Deficient", _s = 2 * _n, "Perfect", _s > 2 * _n, "Abundant")
 )
 )
)
Excel solution 11 for Classify Deficient Perfect Abundant, proposed by Charles Roldan: 
=LET(Answers, {"Deficient";"Perfect";"Abundant"}, 
Nums, A2:A10, Denoms, 1/SEQUENCE(, MAX(Nums)), 
INDEX(Answers, 2+SIGN(-2+MMULT(
DELTA(0, MOD(Nums*Denoms, 1)), TOCOL(Denoms)))))
Excel solution 12 for Classify Deficient Perfect Abundant, proposed by JvdV -: 
=MAP(A2:A10,LAMBDA(s,LET(x,s/SEQUENCE(s),TEXT(SUM(x*(INT(x)=x))/2-s,"[<0]""Deficient"";[=0]Perfect;""Abundant"""))))
For my locale the current escaped characters work. I do realize that there are locale setting using other metacharacters that might need escaping.
A little less verbose and more mainstream I suppose is to create another variable and nest two IF():
=MAP(A2:A10,LAMBDA(s,LET(x,s/SEQUENCE(s),y,SUM(x*(INT(x)=x))/2-s,IF(y<0,"Deficient",IF(y,"Abundant","Perfect")))))
Excel solution 13 for Classify Deficient Perfect Abundant, proposed by Pieter de Bruijn: 
=MAP(A2:A10,LAMBDA(n,LET(s,n/SEQUENCE(n/2),CHOOSE(2+SIGN(1+SUM(s*(MOD(s,1)=0))-(n*2)),"Deficient","Perfect","Abundant"))))
Excel solution 14 for Classify Deficient Perfect Abundant, proposed by Giorgi Goderdzishvili: 
=LET(
number,A2,
seq,SEQUENCE(,number),
divisors,SUM(IF(MOD(number,seq)=0,seq,0)),
logic,
IF(divisors<2*number,"Deficient",
IF(divisors=2*number,"Perfect","Abundant")),
logic)
Excel solution 15 for Classify Deficient Perfect Abundant, proposed by Daniel Garzia: 
=MAP(A2:A10,LAMBDA(l,LET(d,SEQUENCE(l),s,SUM(IF(MOD(l,d),0,d)),t,l*2,IFS(s
Excel solution 16 for Classify Deficient Perfect Abundant, proposed by Quadri Olayinka Atharu: 
=MAP(A2:A10,LAMBDA(n,LET(m,n/SEQUENCE(n),s,SUM(FILTER(m,(INT(m)=m))),IFS(s<2*(n),"Deficient",s=2*(n),"Perfect",s>2*(n),"Abundant"))))
Excel solution 17 for Classify Deficient Perfect Abundant, proposed by Quadri Olayinka Atharu: 
=MAP(A2:A10,LAMBDA(n,LET(m,n/SEQUENCE(n),s,SUM(m*(TRUNC(m)=m)),
IFS(s<2*(n),"Deficient",s=2*(n),"Perfect",s>2*(n),"Abundant"))))
Excel solution 18 for Classify Deficient Perfect Abundant, proposed by Henriette Hamer: 
=MAP(A2:A10;LAMBDA(_numbers;LET(_number;_numbers;_divisors;SEQUENCE(;_number/2;1;1);_sums;SUM(IF(MOD(_number;_divisors)=0;1;0)*_divisors);IF(_sums<_number;"Deficient";IF(_sums>_number;"Abundant";"Perfect")))))
There comes a time when I will remember that with MAP you need Lambda (so far I've been getting away with using a lot of LET :-) )
Excel solution 19 for Classify Deficient Perfect Abundant, proposed by Surendra Reddy: 
=MAP(A2:A10,LAMBDA(x,LET(s,SUM(FILTER(SEQUENCE(x),MOD(x,SEQUENCE(x))=0)),IF(s>2*x,"Abundant",IF(s<2*x,"Deficient","Pefect")))))
Excel solution 20 for Classify Deficient Perfect Abundant, proposed by Adam Carter: 
=MAP(A2:A10,LAMBDA(x,LET(divs,x/SEQUENCE(x),sum_divs,SUM(FILTER(divs,divs=FLOOR.MATH(divs))),_n,x*2,IF(sum_divs>_n,"Abundant",IF(sum_divs=_n,"Perfect","Deficient")))))

Solving the challenge of Classify Deficient Perfect Abundant with Python in Excel

Python in Excel solution 1 for Classify Deficient Perfect Abundant, proposed by Alejandro Campos: 
def obtener_divisores(n):
 divisores = []
 for i in range(1, n + 1):
 if n % i == 0:
 divisores.append(i)
 return divisores
def clasificar_numero(n):
 divisores = obtener_divisores(n)
 suma_divisores = sum(divisores)
 if suma_divisores < 2 * n:
 clasificacion = "Deficiente"
 elif suma_divisores == 2 * n:
 clasificacion = "Perfecto"
 else:
 clasificacion = "Abundante"
 return suma_divisores, clasificacion
numeros = xl("A2:A10")[0]
data = []
for n in numeros:
 suma_divisores, clasificacion = clasificar_numero(n)
 data.append((n, suma_divisores, clasificacion))
df = pd.DataFrame(data, columns=["Número", "Suma Divisores", "Clasificación"])
df
                    
                  
            
  
                  
    
      
        Show translation

Solving the challenge of Classify Deficient Perfect Abundant with Excel VBA

Excel VBA solution 1 for Classify Deficient Perfect Abundant, proposed by Nicolas Micot: 
VBA solution:
Function f_classification(ByVal nombre As Integer) As String
Dim cpt As Long
For i = 1 To nombre
 If nombre Mod i = 0 Then cpt = cpt + i
Next i
If cpt < 2 * nombre Then
 f_classification = "Deficient"
ElseIf cpt = 2 * nombre Then
 f_classification = "Perfect"
ElseIf cpt > 2 * nombre Then
 f_classification = "Abundant"
End If
End Function

&&&

Leave a Reply