Sine Qua Non
Something just makes me love this.
Wrong Retro
This is so true. Hadn’t thought about it before.
Something just makes me love this.
Okay.
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().
This is so true. Hadn’t thought about it before.
Maybe this kind of quote perhaps signals the reemergence of classic special effects and/or cartooning?
Handy saw it for himself in the footage Abrams screened for him, including a scene that featured a Jawa-like creature popping up out of the desert. Abrams called it “a classic, old-school seesaw puppet. We just buried it in the sand, and Neal Scanlan, the creature guy, pushed down on one side and the thing came up on the other side.” When his team offered to smooth out the effect digitally, Abrams responded, “It’s so old-school and crazy. We could improve this thing, but at some point do we lose the wonderful preposterousness?”
Seriously, where do these guys come from?
Blackberry Smoke is pretty much just bog-standard distortion-heavy Southern rock, but “Good One Comin’ On” will pretty much confer high-proof (literally!), high-spirited redneck status to just about anyone for the full duration of its 3:40 runtime.
I’m moving domain registrars this week, which means I’m moving DNS providers, which means antesonic.org may fail to resolve for some period of time, with a random start time and an unreliable finish time. Do not be alarmed!
I had been wanting another Craftsman rubber lawn hose and saw it go on sale via Slickdeals. $15 off. Got onto Sears and clunked my way into figuring out that I had just over $20 in Shop Your Way rewards thanks to the freezer I bought from them recently. Redeemed those and got my net price to $0, plus shipping. Shipping to home was not free, but store pick-up was complementary, so I set it up and everything confirmed, $0 net.
Then I get a note saying they’re stocked out at the local store (guess they have no inventory control centrally?), and to keep the sale they offered me free ship-to-home. The website took multiple identical link-clicks to make this all happen without forwarding me back to the homepage with no explanation.
So now they’re mailing me a $35 hose for free, all for a few minutes of inconvenience scrolling through the website. I can’t see why they’re struggling.
I remember, in the depths of emo-induced musical ennui, rejecting Death Cab for Cutie sound-unheard, just on the basis of the name and the time period. Now they factor fairly heavily into my Pandora stations, and listening to Transatlanticism as an album instead of being chopped into singles is a quite sublime experience.
I’m a frequent offender, but using the term “honesty” when describing music feels to me like describing wine with flower fragrances. That said, Ben’s voice is just plainly honest, and the musical accompaniment is light-handed in a way that is all the more impressive given that I would never also attach “minimalist” to the description.