fandom math with a vengeance

[scarfman]

The last time I wrote in this journal an Excel formula for tracking time against the broadcast history of Doctor Who through the days, it was a season an hour: =MOD(NOW()*24,34)

I'd previously also reported tracking by a Doctor an hour: =MOD(NOW()*24,11)+1

or by a Doctor a day, for which the formula would be: =MOD(NOW(),11)+1

and had implied tracking by a calendar year an hour: =MOD(NOW()*24,48)

Since last report, I've been switching between tracking formulas fairly often, unable to stay satisfied with any for long. Then, the past few days (perhaps as a function of being at home relatively idle in anticipation of starting a new job next week), I've stopped even trying to stick with the same formula for more than a few hours at a stretch. I've been advancing as quickly, or as slowly, or for as long in a loop describing a season or an incarnation, as whim dictates.

The formula I'm using at the moment looks like this:

=RAND()+INT(MOD((NOW()-40326-19.5/24)*24*4,11))+1

This formula advances through the Doctor's eleven incarnations (MOD(...,11)+1) one every quarter (*4) of an hour(*24), starting today at 7:30 pm (NOW()-40326-19.5/24); except, instead of proceeding linearly through each incarnation's time, it generates random (RAND()+INT(...)) points of that incarnation's time during that incarnation's quarter hour. Just the sort of thing that's all very unintelligible, even to others who also use Excel recreationally, unless you wrote it yourself, I'm sure. And you also may be wondering, "What's the point of generating random values between 4 and 5, when you don't know which points of the Doctor's fourth life those values relate to?" So unless you use Excel very recreationally, you may not want to go on to the rest of this.

Because now's when I point out that I only said the formula I'm using right now "looks like" the one I set off above. This is the one I'm really using:

=(RAND()+INT(MOD(($A$1-40326-19.5/24)*24*4,11)))/11

... where cell A1 contains the formula =NOW(). The above formula, like the formula =RAND() alone, generates values between 0 and 1; but these are values that are proportional to the previous formula's values between 1 and 12. Because the second formula wasn't built to stand on its own. It was built to be read by a third formula, which as written here assumes that the second formula is in cell A42 (cell F4 is also referenced, but the formula there will be described below):

=IF(A42>10/11,(A42-10/11)*11*($F$4-47)+47,0)+IF(A42>10/11,0,IF(A42>9/11,(A42-9/11)*11*4+43,IF(A42>8/11,(A42-8/11)*11+42,IF(A42>7/11,(A42-7/11)*11*8.53+33.47,IF(A42>6.75/11,(A42-6/11)*11*25.88+7.59,IF(A42>6/11,(A42-6/11)*11*4+24,IF(A42>5/11,(A42-5/11)*11*15/7+153/7,0)))))))+IF(A42>5/11,0,IF(A42>4/11,(A42-4/11)*11*20/7+19,IF(A42>3/11,(A42-3/11)*11*7+12,IF(A42>2/11,(A42-2/11)*11*5+7,IF(A42>1/11,(A42-1/11)*11*25/9+38/9,A42*11*38/9)))))

The third formula takes a value between 0 and 1, breaks it down into eleven equal segments, and breaks each of those segments into segments equal to the relevant Doctor incarnation's screen history - in calendar years as opposed to in seasons; where season counts only actual screen time produced but calendar years counts time since series initial broadcast - essentially what the season count would be if Doctor Who had aired continuously. An input into the third formula of 0 through 1/11 will return a value between 0 and 4.2222222, because the Doctor's first incarnation's travels go from when he left Gallifrey with Susan before the programme started until the end of the second of nine serials of Series 4 in 1966. An input of 6/11 through 7/11 will return a value between 24 and 33.47, because the Doctor's seventh incarnation's travels go from the regeneration scene at the beginning of Season 24 through three seasons and through six-plus years of broadcast hiatus until the regeneration scene in the May 14, 1996 movie broadcast.

You may ask, what makes me say that 33.47 equals May 1, 1996? This fourth formula, for determining approximate airdate from the third formula (assumes the third formula is in cell B42):

=(B42-1)*365.25+23338.75

... which from the value 1 returns (when date/time formatted in Excel) the value November 23, 1963, 18:00; from the value 33.4739 returns the value May 14, 1996, 20:12; from the value 47.51119 returns the value May 28, 2010, 23:05. Cell F4 has the inverse formula =($A$1-23338.75)/365.25+1, where cell A1, you already know, has the formula for the current time; so the cell F4 formula returns the current time calendar year value, tonight 47.51.

You may ask, remembering that I usually mention my spreadsheet formulas in the context of tracking chronologies for my crossovers, "If you've got a formula for calculating approximate airdate from this calendar year value, you must also have formulas for calculating things like segmenting seasons into episodes, and calculating the stardates in Star Trek's broadcast history relative to Doctor Who's broadcast history."

(I don't know whether there's anyone asking that, or even reading this far into this entry. No one's commented directly on the math of these journal entries tagged math, and I suspect I'm preaching to the pulpit. But anyway:)

That's right. But that can wait for another post. But if you divided this year into eleven equal segments, midnight tonight would be March 18, 1983, approximately toward the end of episode one of Mawdryn Undead, and sometime between The Wrath of Khan and The Search for Spock.