Excelegance
So, let’s say you have a spreadsheet table of activities, the start date of each of those activities, and the duration of each as well. Each activity has four phases that occur within its duration, which we’ll assume all take about the same amount of time.
Now, running off to the right of that data is a time series, let’s just use Monday’s date for each week for a year. The mission: put a code (A/B/C/D) for each date in the time series to indicate which (if any) phase of an activity is currently going on, for each Monday date listed.
The classical solution would be a nasty nested IF() formula. But we can exploit some lesser-known magic.
The CHOOSE() function, a shoo-in for my top-5 list of boutique broadly-unknown Excel functions, takes a first argument that’s an ordinal, and a variable list of follow-on arguments that are selected and returned based on the value of the ordinal. So, if the first argument is 3, CHOOSE() will return the third argument in the follow-on list. Any ordinal that is less than one or greater than the number of follow-on arguments returns the beloved #VALUE error.
If the start date is in column A, the duration (an integer number of days) is in column B, and the labels are on row 1, then we can write in the first data row (row 2) for the first date in the time series (C1):
=IFERROR(CHOOSE(INT((C$1-$A2)/$B2*4)+1,"A","B","C","D"),"")
Peeling the onion, IFERROR() is the godsend Excel 2007 addition that says “pass the first argument through untouched if not an error, if an error use the second value instead of returning the error.” This fixed a horrible pattern in conditional formulas where you had to run the evaluation twice (once to check for error, once to return the value). We’re just subbing in a blank instead of returning the #VALUE.
Then you have the CHOOSE(), which receives the business of the thing: INT((C$1-$A2)/$B2*4) takes the percentage completion of the activity at the given time point, applies the fractional result to 4 (so allowable values range from 0 to 3.999 within the non-error domain), truncates it downward (now we’re only looking at 0, 1, 2, and 3 within the non-error domain), and adds 1 (so we get 1, 2, 3, 4 in the non-error domain). Then CHOOSE() works its magic and gives us the proper phase code for the 1, 2, 3, and 4.
Why truncate-and-add versus the similarly cute and boutique ROUNDUP() function? ROUNDUP() (which forces an upward round on any fractional number) won’t round 0 up to 1 (nor should it), so you actually get an incorrect answer on the first valid day of the activity. Truncate-and-add shifts this ambiguity to the end of the valid domain, where we’re not as sensitive to it.
Note that over a full time series the values are actually all over the map, evaluating to negative numbers and big numbers for most activities. But the natural guarding of the CHOOSE() function allows us to use its failure as a filter, actually yielding a much more efficient function than you would see in a nested IF().