I applaud the effort to devise your own algorithm for calculating Catholic Easter (CE) based on the paschal full moon (PFM). You also supply the calculation for the Orthodox Easter (OE) PFM that I was looking for, as I had previously retrieved it from a table lookup based on the Golden Number of the year.
I would like to present alternate methods of calculating the CE and OE, which will leverage the LET function available in newer versions of Excel (with dynamic arrays) to compactly apply effective extant algorithms.
Let’s start with CE. We’re going to calculate Easter in two different ways, and if they’re identical we’ll return one of them, otherwise we’ll return an error. Since LET is efficient and does not recalculate variables, there’s virtually no computational cost to doing the comparison beyond just running two algorithms instead of one.
The source for what follows comes from https://en.m.wikipedia.org/wiki/Date_of_Easter#Anonymous_Gregorian_algorithm (and subsequent sections)
The first algorithm has an anonymous origin and was first published in Nature magazine in 1876, then subsequently reprinted numerous times over the years including in Jean Meeus’s 1991 “Astronomical Algorithms”. The second algorithm was introduced by Scottish mathematician Thomas H. O’Beirne in New Scientist in 1961, which simplifies the other algorithm somewhat. Assuming the year is in a cell named “year”:
=LET(meeus,LET(a,MOD(year,19),b,QUOTIENT(year,100),c,MOD(year,100),d,QUOTIENT(b,4),e,MOD(b,4),f,QUOTIENT((b+8),25),g,QUOTIENT((b-f+1),3),h,MOD(a*19+b-d-g+15,30),i,QUOTIENT(c,4),k,MOD(c,4),l,MOD(32+e*2+i*2-h-k,7),m,QUOTIENT(a+h*11+l*22,451),y,h+l-m*7+114,n,QUOTIENT(y,31),o,MOD(y,31),DATE(year,n,o+1)),obeirne,LET(a,MOD(year,19),b,QUOTIENT(year,100),c,MOD(year,100),d,QUOTIENT(b,4),e,MOD(b,4),g,QUOTIENT(b*8+13,25),h,MOD(a*19+b-d-g+15,30),i,QUOTIENT(c,4),k,MOD(c,4),l,MOD(32+e*2+i*2-h-k,7),m,QUOTIENT(a+h*11+l*19,433),n,QUOTIENT(h+l-m*7+90,25),p,MOD(h+l-m*7+n*33+19,32),DATE(year,n,p)),IF(meeus=obeirne,meeus,”Error”))
The variable names have been preserved, except for “y” in the first (“Meeus”) algorithm. That quantity doesn’t have a letter in the original algorithm, but it is useful to assign it one because the number it produces is one of 35 possible values from 114 to 148, representing the date of Easter, each of which corresponds, in sequence, to the dates of March 22nd to April 25th. So once you have the quantity “y”, you can alternatively retrieve the month and day via a 35-row table lookup instead of with QUOTIENT and MOD (+1) respectively.
Next, we’ll turn our attention to the OE and apply the same 2-in-1 approach for the calculation. The first algorithm comes from Meeus’s book again, though it is possible it originates elsewhere. The second algorithm uses the PFM calculation mentioned in this very article.
Since we want the Gregorian date of the OE, we require an adjustment for the discrepancy between the Julian and Gregorian calendars. From 1900 to 2099, that value is 13 (days). It increases by 1 every century year, except those divisible by 400. We can calculate this value with the formula:
=QUOTIENT(year,100)-(QUOTIENT(year,400)+2)
Although we can run the calculation inside the LET function itself, it is probably a good idea to have that number displayed separately so we know what it is. Therefore, we’ll calculate it next to the year somewhere, and in the OE formula we’ll merely reference it the same way we’re referencing the year (we’ll call this one “julian_delta”).
=LET(meeus,LET(a,MOD(year,4),b,MOD(year,7),c,MOD(year,19),d,MOD((c*19+15),30),e,MOD((a*2+b*4-d+34),7),y,d+e+114,f,QUOTIENT(y,31),g,MOD(y,31),DATE(year,f,g+1)+julian_delta),pfm,LET(pfm,DATE(year,3,21)+MOD(15-MOD(year,19)*11,30)+julian_delta,pfm+8-WEEKDAY(pfm)),IF(meeus=pfm,meeus,”Error”))
Once again, the variable names have been preserved (in just the first algorithm this time), except “y” which has been discussed previously.
* * *
Let’s now apply the aforementioned algorithms to *arrays* of years that output the dates of Easter not just for a given year, but for every year from 1900 until the year in question.
On a separate worksheet, we’ll title columns A:D (in cells A1 through D1) in order something like: Year, C_Easter, JΔ, O_Easter. Below them, in cells A2:D2, we’ll have formulas for four different arrays (if you have the newest versions of Excel you can subsequently merge them all with HSTACK).
In A2, (assuming “year” has a global scope, otherwise prepend with “[worksheet_name]!”) we have:
=SEQUENCE(year-1899,1,1900)
The second array will calculate the CE dates. In B2 we have:
=LET(meeus,LET(a,MOD(A2#,19),b,TRUNC(A2#/100,0),c,MOD(A2#,100),d,TRUNC(b/4,0),e,MOD(b,4),f,TRUNC((b+8)/25,0),g,TRUNC((b-f+1)/3,0),h,MOD(a*19+b-d-g+15,30),i,TRUNC(c/4,0),k,MOD(c,4),l,MOD(32+e*2+i*2-h-k,7),m,TRUNC((a+h*11+l*22)/451,0),y,h+l-m*7+114,n,TRUNC(y/31,0),o,MOD(y,31),DATE(A2#,n,o+1)),obeirne,LET(a,MOD(A2#,19),b,TRUNC(A2#/100,0),c,MOD(A2#,100),d,TRUNC(b/4,0),e,MOD(b,4),g,TRUNC((b*8+13)/25,0),h,MOD(a*19+b-d-g+15,30),i,TRUNC(c/4,0),k,MOD(c,4),l,MOD(32+e*2+i*2-h-k,7),m,TRUNC((a+h*11+l*19)/433,0),n,TRUNC((h+l-m*7+90)/25,0),p,MOD(h+l-m*7+n*33+19,32),DATE(A2#,n,p)),IF(meeus=obeirne,meeus,”Error”))
Notice that we’re replacing QUOTIENT with TRUNC because the former does not seem to work on an element-wise basis in arrays (unlike MOD which works natively with arrays just as well as with scalars). QUOTIENT is probably an option with BYROW and LAMBDA if those newer functions are available in whatever version of Excel you’re working with, but TRUNC achieves the same result.
For OE, we’re going to use the same approach of displaying the Julian/Gregorian discrepancy separately so we have that visual feedback. In C2 we have:
=TRUNC(A2#/100,0)-(TRUNC(A2#/400,0)+2)
Then we calculate OE in D2 as follows:
=LET(meeus,LET(a,MOD(A2#,4),b,MOD(A2#,7),c,MOD(A2#,19),d,MOD((c*19+15),30),e,MOD((a*2+b*4-d+34),7),y,d+e+114,f,TRUNC(y/31,0),g,MOD(y,31),DATE(A2#,f,g+1)+C2#),pfm,LET(pfm,DATE(A2#,3,21)+MOD(15-MOD(A2#,19)*11,30)+C2#,pfm+8-WEEKDAY(pfm)),IF(meeus=pfm,meeus,”Error”))