81- Aggregate, to e.g. find the next birthday (Excel)
Using an arcane, unknown function (Aggregate) to do some array stuff.
Excel pre-365 doesn't like arrays.
In this case I want to find the first birthday in the future.
I first need to remove the year portion from birthdays, since otherwise minimum would return the oldest person.
Then I need to find the minimum.
You can use Minimum instead, but you need to remove errors e.g. with IFError or outputting a large number instead of False/0.
Afterwards you need to remember to press CSE to enter the function.
I borrow some tricks from ExcelJet.
Aggregate
https://exceljet.net/functions/aggreg...
Multiple conditions using division by boolean + doing a match for 1
https://exceljet.net/formulas/xlookup...
00:40 Two problems
01:05 Helper columns to visualize
02:15 Only dates in the future
02:30 Divide by a boolean
03:07 Min doesn't like errors
03:58 Aggregate, the OG array function
07:17 Match 1
08:53 Match with CSE
09:45 Min with CSE
10:54 IFError before Min
11:34 Adding a comment with N