If either value changes, the formula should generate a new list of leap years.
Then it checks for leap years using logic implemented with the MOD function and filters out all non-leap years.
At the start, theLET functiondefines four variables:
Using LET this way keeps the formula efficient and readable.
We can do this with theSEQUENCE function, which is designed to create numeric arrays.
Testing for leap years
The next step is to test each year inyearsto identify leap years.
The array is assigned to theleapsvariable defined by LET.
The FILTER function uses theleapsarray to filter out non-leap years.
The final result is an array that contains only leap years.
This array lands in cell D5 and spills down the worksheet.
We can, but we need to adjust the formula first.
AND and OR are “aggregating functions”, which means they return asingleaggregated result.
The result for each year is asingleTRUE or FALSE value.
The BYROW function packages the results into a single array, which we hand off to FILTER as before.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
…
LET Function
The Excel LET function lets you define named variables in a formula.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
For example, MOD(10,3) = 1.
The result of MOD carries the same sign as the divisor.
BYROW can apply stock functions like SUM, COUNT, and AVERAGE or a custom LAMBDA function.
AND returns TRUEonly if all the conditions are met.
If any conditions are not met, the AND function returns FALSE.
OR returns TRUEif any condition is TRUE.
If all conditions are FALSE, the OR function returns FALSE.
