Home » Find Divisor Square Sums

Find Divisor Square Sums

Find all the factors of a number, square these factors and sum which is called Divisor Square Sum. List first 50 numbers whose Divisor Square Sum is a perfect square. (In case, 50 is a big ask, you may limit it to 20 also) Ex. 42 – Factors of 42 are 1, 2, 3, 6, 7, 14, 21, 42. Sum of squares is 2500 which is a perfect square.

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

Solving the challenge of Find Divisor Square Sums with Power Query

Power Query solution 1 for Find Divisor Square Sums, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = List.Transform(
    List.Select(
      List.Generate(
        () => [x = 1, n = 1, m = 1], 
        each [n] <= 25, 
        each [
          x = [x] + 1, 
          z = Number.Round(Number.Sqrt(x)), 
          k = List.Select({1 .. z}, each Number.Mod(x, _) = 0), 
          l = Number.Sqrt(
            List.Sum(
              List.Transform(
                List.Distinct(List.Transform(k, each x / _) & k), 
                each Number.Power(_, 2)
              )
            )
          ), 
          m = if Number.Round(l) = l then 1 else null, 
          n = if m = null then [n] else [n] + 1
        ], 
        each {[x], [m] = 1}
      ), 
      each _{1}
    ), 
    each _{0}
  )
in
  Source
Power Query solution 2 for Find Divisor Square Sums, proposed by Abdallah Ally: 
let
  sumOfSqDivsPerfect = (num) =>
    [
      a = Number.RoundDown(Number.Sqrt(num)), 
      b = List.Select({1 .. a}, each Number.Mod(num, _) = 0), 
      c = List.Accumulate(
        b, 
        0, 
        (x, y) => x + [u = num / y, v = y * y + Byte.From(y <> u) * u * u][v]
      ), 
      d = Number.RoundDown(Number.Sqrt(c)), 
      e = d * d = c
    ][e], 
  Generate = List.Generate(
    () => [num = 1, cond = sumOfSqDivsPerfect(num), count = Byte.From(cond)], 
    each [count] <= 50, 
    each [num = [num] + 1, cond = sumOfSqDivsPerfect(num), count = [count] + Byte.From(cond)], 
    each if [cond] then [num] else null
  ), 
  Result = Table.FromColumns({List.RemoveNulls(Generate)}, {"Answer Expected"})
in
  Result
Power Query solution 3 for Find Divisor Square Sums, proposed by Mihai Radu O: 
let
  fct = (nr) =>
    [
      a = Number.RoundDown(Number.Sqrt(nr), 0), 
      b = List.Select({1 .. a}, (x) => Number.Mod(nr, x) = 0), 
      c = List.Transform(b, (x) => nr / x), 
      d = Number.Sqrt(List.Sum(List.Transform(List.Distinct(b & c), (x) => Number.Power(x, 2)))), 
      e = d = Int64.From(d)
    ][e], 
  a = List.Generate(
    () => [x = 1, y = 0], 
    each [y] <= 20, 
    each [x = [x] + 1, y = [y] + Byte.From(fct([x]))], 
    each if fct([x]) then [x] else null
  ), 
  b = List.RemoveNulls(a)
in
  b
Power Query solution 4 for Find Divisor Square Sums, proposed by Tyler N.: 
List.RemoveNulls(
  List.Transform(
    {1 .. 2544697}, 
    (x) =>
      if Number.Mod(
        Number.Sqrt(
          List.Sum(
            List.Transform(
              {1 .. x}, 
              each if Number.Mod(x / _, 1) <> 0 then 0 else Number.Power(x / _, 2)
            )
          )
        ), 
        1
      )
        <> 0
      then
        null
      else
        x
  )
)

Solving the challenge of Find Divisor Square Sums with Excel

Excel solution 1 for Find Divisor Square Sums, proposed by Bo Rydobon 🇹🇭: 
=TOCOL(MAP(SEQUENCE(
    10^5
),
    LAMBDA(n,
    LET(s,
    SEQUENCE(
        n^0.5
    ),
    n/(MOD(
        SUMSQ(
            UNIQUE(
                TOCOL(
                    IFS(
                        MOD(
                            n,
                            s
                        )=0,
                        HSTACK(
                            s,
                            n/s
                        )
                    ),
                    3
                )
            )
        )^0.5,
        1
    )=0)))),
    3)
Excel solution 2 for Find Divisor Square Sums, proposed by Timothée BLIOT: 
=LET(A,SEQUENCE(25000),B,MAP(A,LAMBDA(x,SUM(VSTACK(x,IFERROR(FILTER(SEQUENCE(x/2),MOD(x,SEQUENCE(x/2))=0,0),0))^2)))^0.5,FILTER(A,INT(B)=B))
Excel solution 3 for Find Divisor Square Sums, proposed by LEONARD OCHEA 🇷🇴: 
=TOCOL(MAP(SEQUENCE(
    10^4
),
    LAMBDA(x,
    LET(s,
    SEQUENCE(
        x
    ),
    t,
    SUMSQ(TOCOL(s/(FLOOR(
        x,
        s
    )=x),
    3))^0.5,
    x/(t=INT(
        t
    ))))),
    3)
Excel solution 4 for Find Divisor Square Sums, proposed by ferhat CK: 
=TOCOL(
    MAP(
        SEQUENCE(
            10000
        ),
        LAMBDA(
            y,
            LET(
                t,
                SEQUENCE(
                    y
                ),
                f,
                FILTER(
                    t,
                    MOD(
                        y,
                        t
                    )=0
                ),
                k,
                SUMSQ(
                    f
                )^0.5,
                IF(
                    k=INT(
                        k
                    ),
                    y,
                    1/0
                )
            )
        )
    ),
    2
)

Solving the challenge of Find Divisor Square Sums with Python

Python solution 1 for Find Divisor Square Sums, proposed by Abdallah Ally: 
highly optimized
import pandas as pd
from math import isqrt
def get_sum_square_divisors(num):
 sum_sq_divs = 0
 for i in range(1, isqrt(num) + 1):
 if num % i == 0:
 sum_sq_divs += i * i
 if i != num // i:
 sum_sq_divs += (num // i) ** 2
 return sum_sq_divs
def get_numbers(count):
 numbers = []
 num = 1
 while len(numbers) < count:
 sum_sq_divs = get_sum_square_divisors(num)
 if isqrt(sum_sq_divs) ** 2 == sum_sq_divs:
 numbers.append(num)
 num += 1
 return numbers
# Perform data manipulation
df = pd.DataFrame(data={'Answer Expected': get_numbers(50)})
df

Solving the challenge of Find Divisor Square Sums with Python in Excel

Python in Excel solution 1 for Find Divisor Square Sums, proposed by Aditya Kumar Darak 🇮🇳: 
import math
import itertools
def MyFun(count):
 def divisor_square_sum():
 for n in itertools.count(1):
 nums = np.arange(1, int(n**0.5) + 1)
 factors = nums[n % nums == 0]
 allFactors = np.unique(np.concatenate((factors, n // factors)))
 dvsrSqrSum = np.sum(allFactors**2)
 if math.isqrt(dvsrSqrSum) ** 2 == dvsrSqrSum:
 yield n
 return list(itertools.islice(divisor_square_sum(), count))
result = MyFun(50)
Python in Excel solution 2 for Find Divisor Square Sums, proposed by Aditya Kumar Darak 🇮🇳: 
import math
import itertools
def MyFun(count):
 def divisor_square_sum():
 n = 1
 while True:
 nums = np.arange(1, int(n**0.5) + 1)
 factors = nums[n % nums == 0]
 allFactors = np.unique(np.concatenate((factors, n // factors)))
 dvsrSqrSum = np.sum(allFactors**2)
 if math.isqrt(dvsrSqrSum) ** 2 == dvsrSqrSum:
 yield n
 n += 1
 return list(itertools.islice(divisor_square_sum(), count))
result = MyFun(50)
Python in Excel solution 3 for Find Divisor Square Sums, proposed by Owen Price: 

10 numbers - less than a quarter of a second
20 numbers - about 3 seconds
30 numbers - about 17 seconds
40 numbers - about 44 seconds
50 numbers - about 125 seconds

Solving the challenge of Find Divisor Square Sums with Excel VBA

Excel VBA solution 1 for Find Divisor Square Sums, proposed by Md. Zohurul Islam: 
Sub ExcelBI_ExcelChallenge625()
 'Find Perfect Square DivisorSums
 Dim num As Long
 Dim count As Long
 Dim divisorSum As Long
 Dim i As Long
 Dim j As Long
 Dim results As Collection
 
 'headers
 Range("D1") = "VBA Solution"
 
 ' Initialize variables
 count = 0
 Set results = New Collection
 
 For num = 1 To 30000
 ' Find factors and calculate Divisor Square Sum
 divisorSum = 0
 
 ' Find factors of num
 For i = 1 To Sqr(num)
 If num Mod i = 0 Then
 ' i is a factor
 divisorSum = divisorSum + (i * i)
 
 ' Check if num/i is different from i to avoid duplication
 If i <> num / i Then
 divisorSum = divisorSum + ((num / i) * (num / i))
 End If
 End If
 Next i
 
 If IsPerfectSquare(divisorSum) Then
 results.Add num
 
 ' Increment count and check if we have found enough results
 count = count + 1
 
 If count >= 50 Then Exit For
 End If
 Next num
 
 'Output
 For j = 1 To results.count
 Range("D" & j + 1) = results(j)
 Next j
 
End Sub
Excel VBA solution 2 for Find Divisor Square Sums, proposed by Md. Zohurul Islam: 
Function IsPerfectSquare(ByVal n As Long) As Boolean
 Dim root As Long
 root = Sqr(n)
 IsPerfectSquare = (root * root = n)
End Function

&&&

Leave a Reply