Next Perfect Square Finder - Excel & Python Challenge

Find the next perfect square numbers. If number itself is a perfect square, then also answer has to be next perfect square number not that number itself.

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

Solving the challenge of Next Perfect Square Finder with Power Query

Power Query solution 1 for Next Perfect Square Finder, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Return = Table.AddColumn(
    Source, 
    "Answer", 
    each Number.Power(Number.RoundDown(Number.Sqrt([Number]) + 1), 2)
  )
in
  Return

Power Query solution 2 for Next Perfect Square Finder, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol = Table.AddColumn(
    Source, 
    "Answer", 
    each 
      let
        a = Number.RoundDown(Number.Sqrt([Number])), 
        b = Number.Power(a + 1, 2)
      in
        b
  )
in
  Sol

Power Query solution 3 for Next Perfect Square Finder, proposed by Ramiro Ayala Chávez:

let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol = Table.AddColumn(
    S, 
    "Answer Expected", 
    each Number.Power(Number.RoundDown(Number.Power([Number], 0.5)) + 1, 2)
  )
in
  Sol

Power Query solution 4 for Next Perfect Square Finder, proposed by Rafael González B.:

let
 Source = Excel.CurrentWorkbook(){0}[Content][Number],
 LG = List.Generate(
 () => [c = -1, L = {}],
 each [c] <= List.Count(Source) - 1,
 each [
 c = [c] + 1,
 L = Number.Power(Number.RoundDown(Number.Sqrt(Source{c})) + 1,2)
 ],
 each [L]
 )
 
in
 Table.FromList(List.Skip(LG), Splitter.SplitByNothing(), {"Answer Expected"})
🧙🏻‍♂️🧙🏻‍♂️🧙🏻‍♂️

Power Query solution 5 for Next Perfect Square Finder, proposed by Rafael González B.:

let
 Source = Excel.CurrentWorkbook{0}[Content][Number],

 LA = Table.FromList(List.Accumulate(
 Source,
 {},
 (s,c) => 
 let
 a = Number.RoundDown(Number.Sqrt(c)) + 1,
 b = Number.Power(a,2)
 in 
 s & {b}
 ), Splitter.SplitByNothing(), {"Answer Expected"})
 
in
 LA

🧙🏻‍♂️🧙🏻‍♂️🧙🏻‍♂️

Power Query solution 6 for Next Perfect Square Finder, proposed by Rafael González B.:

let
 Source = Excel.CurrentWorkbook(){0}[Content],
 Result = Table.AddColumn(Source, "Answer Expected", each 
 let
 N = [Number],
 NS = Number.RoundDown(Number.Sqrt(N)) + 1,
 NP = Number.Power(NS, 2)
 in
 NP
 )[[Answer Expected]]
in
 Result

🧙🏻‍♂️🧙🏻‍♂️🧙🏻‍♂️

Power Query solution 7 for Next Perfect Square Finder, proposed by Venkata Rajesh:

let
  Source = Data, 
  Output = Table.AddColumn(
    Source, 
    "Expected", 
    each [sqrt = Number.Sqrt([Number]), num = Number.RoundDown(sqrt) + 1, nextSquare = num * num][
      nextSquare
    ]
  )
in
  Output

Solving the challenge of Next Perfect Square Finder with Excel

Excel solution 1 for Next Perfect Square Finder, proposed by Rick Rothstein:

=(INT(
    A2:A10^0.5
)+1)^2

Edit Note: A little bit shorter...
=INT(
    1+A2:A10^0.5
)

Excel solution 2 for Next Perfect Square Finder, proposed by Rick Rothstein:

=LET(
    s,
    SEQUENCE(
        100000
    )^2,
    INDEX(
        s,
        XMATCH(
            A2:A10+1,
            s,
            1
        )
    )
)

Excel solution 3 for Next Perfect Square Finder, proposed by John V.:

=INT(
    1+A2:A10^0.5
)

Excel solution 4 for Next Perfect Square Finder, proposed by محمد حلمي:

=INT(
    A2:A10^0.5+1
)

Excel solution 5 for Next Perfect Square Finder, proposed by Julian Poeltl:

=INT(
    SQRT(
        A2:A10
    )+1
)

Excel solution 6 for Next Perfect Square Finder, proposed by Aditya Kumar Darak 🇮🇳:

=INT(
    SQRT(
        A2:A10
    ) + 1
)

Excel solution 7 for Next Perfect Square Finder, proposed by Nikola Z Grujicic - Nikola Ž Grujičić:

=MAP(
    A2:A10,
     LAMBDA(
         a,
          ""&POWER(
              INT(
                  SQRT(
                      a
                  )
              )+1,
              2
          )
     )
)

Excel solution 8 for Next Perfect Square Finder, proposed by Sunny Baggu:

=POWER(
    1+INT(
        SQRT(
            A2:A10
        )
    ),
    2
)

Excel solution 9 for Next Perfect Square Finder, proposed by Sunny Baggu:

=POWER(1 + INT(SQRT(A2:A10)), 2) + N("💐")

Excel solution 10 for Next Perfect Square Finder, proposed by Sunny Baggu:

=POWER(
    1+TRUNC(
        SQRT(
            A2:A10
        )
    ),
    2
)

Excel solution 11 for Next Perfect Square Finder, proposed by Abdallah Ally:

Excel solution 12 for Next Perfect Square Finder, proposed by Anshu Bantra:

=(INT(
    SQRT(
        A2:A10
    )
) + 1)

Excel solution 13 for Next Perfect Square Finder, proposed by 🇵🇪 Ned Navarrete C.:

=TRUNC(
    A2:A10^0.5+1,
    
)

Excel solution 14 for Next Perfect Square Finder, proposed by Andy Heybruch:

=(ROUNDDOWN(
    A2:A10^0.5,
    0
)+1)

Excel solution 15 for Next Perfect Square Finder, proposed by Bilal Mahmoud kh.:

POWER(
    ROUNDDOWN(
        SQRT(
            A2:A10
        ),
        0
    )+1,
    2
)

Excel solution 16 for Next Perfect Square Finder, proposed by CA Raghunath Gundi:

=(TRUNC(
    SQRT(
        A2:A10
    ),
    0
)+1)

Excel solution 17 for Next Perfect Square Finder, proposed by Mey Tithveasna:

=INT(
    SQRT(
        A2:A10
    )+1
)

Excel solution 18 for Next Perfect Square Finder, proposed by Oscar Javier Rosero Jiménez:

ROUNDDOWN((SQRT(
    A2:A10
)+1),
    0)^2
ROUNDDOWN(
    A2:A10^0.5+1,
    0
)^2

Excel solution 19 for Next Perfect Square Finder, proposed by Edwin Tisnado:

=(INT((B2:B10+0.5)^0.5)+1)

Excel solution 20 for Next Perfect Square Finder, proposed by Ernesto Vega Castillo:

=POTENCIA(
    ENTERO(
        RAIZ(
            A2:A10
        )+1
    ),
    2
)

### Code -En-
=POWER(
    INT(
        SQRT(
            A2:A10
        )+1
    ),
    2
)

Excel solution 21 for Next Perfect Square Finder, proposed by Burhan Cesur:

=MAP(A2:A10,
    LAMBDA(x,
    (INT(
        SQRT(
            x
        )
    )+1)^2))
or
=(INT(
    SQRT(
        A2:A10
    )
)+1)

Excel solution 22 for Next Perfect Square Finder, proposed by Josh Brodrick:

=(ROUNDDOWN(
    SQRT(
        A2:A10
    ),
    0
)+1)

Excel solution 23 for Next Perfect Square Finder, proposed by Enrico Giorgi:

=POWER(
    INT(
        SQRT(
            A2:A10
        )
    )+1,2
)

ITALIAN VERSION
 =POTENZA(
     INT(
         RADQ(
            A2:A10
        )
     )+1;
     2
 )

Excel solution 24 for Next Perfect Square Finder, proposed by Tyler Cameron:

Excel solution 25 for Next Perfect Square Finder, proposed by Moshe Moses, FCCA:

MAP(A2:A10,
    LAMBDA(x,
    (ROUNDDOWN(
        SQRT(
            x
        ),
        0
    )+1)^2))

Excel solution 26 for Next Perfect Square Finder, proposed by Tsiory RAZAFITSEHENO:

Xlookup(
    A2:A10+0,1;
    SEQUENCE(
        100000
    )^2;
    SEQUENCE(
        100000
    )^2;
    ;
    1
)

Excel solution 27 for Next Perfect Square Finder, proposed by Ben Gutscher:

=INT(
    A2:A10^0.5+1
)

Excel solution 28 for Next Perfect Square Finder, proposed by rhonal chairul:

=TRUNC(
    SQRT(
        A2
    )+1
)

Solving the challenge of Next Perfect Square Finder with Python

Python solution 1 for Next Perfect Square Finder, proposed by Konrad Gryczan, PhD:

import pandas as pd
import math
input = pd.read_excel("459 Next Perfect Square.xlsx", usecols="A")
test  = pd.read_excel("459 Next Perfect Square.xlsx", usecols="B")
def find_next_perf_square(n):
 return (math.floor(math.sqrt(n)) + 1) ** 2
result = input["Number"].apply(find_next_perf_square)
print(result.equals(test["Answer Expected"])) # True

Solving the challenge of Next Perfect Square Finder with Python in Excel

Python in Excel solution 1 for Next Perfect Square Finder, proposed by Abdallah Ally:

s = xl("A2:A10")
s = s.map(lambda x: (int(x ** 0.5) + 1) ** 2)
s

Python in Excel solution 2 for Next Perfect Square Finder, proposed by Abdallah Ally:

import pandas as pd
file_path = 'Excel_Challenge_459 - Next Perfect Square.xlsx'
df = pd.read_excel(file_path)
# Perform data wrangling
df['My Answer'] = df['Number'].map(lambda x: (int(x ** 0.5) + 1) ** 2)
df['Check'] = df['Answer Expected'] == df['My Answer']
df

Python in Excel solution 3 for Next Perfect Square Finder, proposed by ferhat CK:

import math
[mat&h.ceil(math.sqrt(n+1)) ** 2 for n in xl("A2:A10")[0]]

Solving the challenge of Next Perfect Square Finder with R

R solution 1 for Next Perfect Square Finder, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)
input = read_excel("Excel/459 Next Perfect Square.xlsx", range = "A1:A10")
test = read_excel("Excel/459 Next Perfect Square.xlsx", range = "B1:B10")
find_next_perf_square = function(n) (floor(sqrt(n)) + 1) ** 2
result = input %>%
 mutate(`Answer Expected` = map_dbl(Number, find_next_perf_square)) %>%
 select(-Number)
identical(result, test)
# [1] TRUE

Solving the challenge of Next Perfect Square Finder with Excel VBA

Excel VBA solution 1 for Next Perfect Square Finder, proposed by Rushikesh K.:

Sub SquareAndRoundup()
 Dim ws As Worksheet
 Dim lastRow As Long
 Dim i As Long
 Dim num As Double
 Dim sqrtNum As Double
 Dim roundedNum As Double
 Dim squaredNum As Double
 
 Set ws = ThisWorkbook.Sheets("Sheet1")
 
 lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
 
 For i = 2 To lastRow
 num = ws.Cells(i, 1).Value
 
 sqrtNum = Sqr(num)
 
 roundedNum = Application.WorksheetFunction.RoundUp(sqrtNum, 0)
 
 squaredNum = roundedNum ^ 2
 
 ws.Cells(i, 2).Value = squaredNum
 Next i
End Sub