Iterative calculations

I used iterative calculation in Sudoku solution. Here's another example of its use.

A prime number is divisible only by itself and 1 meaning the remainder of dividing a prime number by any other integer greater than 1 is always greater than 0.

Algorithm

1. set n = 500,000,000,001
2. for all integers i between 2 and √n, if remainder of any n÷i is greater than 0
3. then show n as result else set n = n + 1 and goto step 2 .

now over to spreadsheet.

---

Enable iterative calculations

• Navigate File > Options > Formulas
• Then, Check “Enable iterative calculations“ in Calculation options
• set maximum iterations to 100 (this can be changed later)

---

set n = 500,000,000,001

avoid cryptic formulas - use named ranges

First, I'll name range A1 as n and enter 500,000,000,001

---

for all integers i between 2 and √n

Lets use SEQUENCE function to create an array i with a range from 2 to √n beginning with 2 with and an increment of 1 at each step.

syntax is SEQUENCE(rows,[columns],[start],[step]), so our formula is:

• i = SEQUENCE(SQRT(n)-2,1,2,1)

meaning we create a sequence with SQRT(n) - 2 rows and 1 column beginning with 2 and an increment of 1 at each step.

This sequence is 707,105 rows long (2 to 707107)! Names ranges can help us use this array without storing the entire range in spreadsheet cells. To do this, navigate to formulas tab and click define name then in the name range window

• name = i
• Refers to = SEQUENCE(SQRT(n)-2,1,2,1)

Now excel identifies i as a range from 2 to 707107 with increment of 1 at each step!

---

Notes on MOD function

Excel's MOD function returns the remainder after number is divided by divisor.

In our case MOD(n,i) will return an array of 707,104 remainders. But we only need to know if any of those remainders is greater than 0. So, we'll use the MIN function (Minimum) to call the lowest number in the array.

• MIN(MOD(n,i))

---

if remainder of any n÷i is greater than 0

IF function will to check if remainder of any n÷i is greater than 0

• IF(MIN(MOD(n,i)>0) But! this formula is incomplete... MIN(MOD(n,i)>0 is the logical test. To complete the formula, we need value if true and value if false

---

then show n as result

if MIN(MOD(n,i) > 0 then we must return n as result... so we complete this in the IF function syntax

• IF(MIN(MOD(n,i))>0,n,)

this function is till not complete

---

else set n = n + 1

finally, with the else condition, the syntax is complete

• IF(MIN(MOD(n,i))>0,n,n+1)

I'll enter this formula into a name range called p

---

and go-to step 2

To do this, go back to range n and update its formula as =p

and there's the result - 500,000,000,023

* The post Iterative Calculations first appeared on continuoous.com