How to compose readable excel formulas

Unsplash

  1. Excel formulas are hard to read. Case in point, the formula below evaluates if value in A1 is a prime number. LET function can re-compose it to an intuative form.
    =(MIN(MOD(A1,SEQUENCE(SQRT(A1),,2,1)))<>0)

  2. Three points before we begin. First, Prime number is an integer greater than one which is only divisible by itself and \(1\). Put another way, the remainder of dividing a prime number by any integer other other than \(1\) never equals \(0\).

  3. Next, LET function assigns names to calculation results. I’ll let the picture explain it best. LetFunc

  4. Finally, Primality test is an algorithm to determine if a given number, \(n\), is prime. If \(n\) is evenly divisible by any number between \(2\) and \(\sqrt n\). then it is non-prime. Else, it is prime.

  5. Primality test for a number \(n\) follows below sequence.
    • calculate \(\sqrt n\).
    • create an integer array {\(2...\sqrt n\)}
    • divide \(n\) by array {\(2...\sqrt n\)} to create new array of remainders
    • check if minimum value in the array of remainders is \(0\)
    • if No, then \(n\) is prime.
  6. (MIN( MOD( A1, SEQUENCE( SQRT(A1),,2,1 ) ) )<>0) implements these steps except that its hard to see how. LET function is more intuitive in following the sequence of steps.
    =LET(
    Step0,A1,
    Step1,SQRT(Step0),
    Step2,SEQUENCE(Step1,,2,1),
    Step3,MOD(Step0,Step2),
    Step4,MIN(Step3),
    Step5,(Step4<>0),
    Step5
    )
    
  7. Here’s how to read the formula

    • Step0,A1,

      Step0 \(\equiv\) value in A1

    • Step1,SQRT(Step0),

      Step1 \(\equiv\) square root of Step0

    • Step2,SEQUENCE(Step1,,2,1),

      Step2 \(\equiv\) array of integers from \(2\) to Step1 with increment of \(1\)

    • Step3,MOD(Step0,Step2),

      Step3 \(\equiv\) array of remainders resulting from Step0/Step2

    • Step4,MIN(Step3),

      Step4 \(\equiv\) minimum value in Step3, i.e. array of remainders

    • Step5,(Step4<>0),

      Step5 \(\equiv\) check of equality if Step4 <> \(0\)

    • Step5

      Show Step5

  8. LET function is more legible and improves Excel calculation speed. However, nested formula are easier to compose. The best practice might be to compose nested formulas and then convert those to LET functions.


Demo File Here