bride of fandom with math

[scarfman]

In closing two recent regular LJ entries (as opposed to entries that are twit mirrors) tagged math, I've promised an entry about Excel spreadsheet formulas correlating the chronologies of other shows to the chronology notation I use for Doctor Who. Even casual readers of my twits or LJ will know that I use this spreadsheet to correlate shows' chronologies for my fanfiction, but all I've ever described in detail here is the way I manipulate the Doctor Who notation for my own entertainment to pass the time. I say "recent", but those journal entries with that promise both date to 2010, and I never delivered on that promise. But now someone's actually asked, so here we go.

First a little review: For the purposes of fanfiction chronology I track the total years since Doctor Who began airing, whether it actually aired any given year or not. The Excel formula for this is:

=(NOW()-(DATE(1963,11,23)+TIME(18,0,0)))/365.25+1

or, more simply,

=(NOW()-23338.75)/365.25+1

Actually, in my spreadsheet, I have the current date and time in Cell A1, so my formula really is

=($A$1-23338.75)/365.25+1

I keep this formula in Cell F4.

With the date November 23 1963 at 18:00 in Cell A1, the Cell F4 formula returns the figure 1, signifying the beginning of the first season (or, if you're British, series) of Doctor Who. For the date December 2 2011 22:00 this formula returns the figure (rounded to two decimal places) 49.03, about a week and a half into Doctor Who's forty-ninth year. 4.22 signifies the first regeneration, 16.99 signifies the completion of the Key to Time quest, 33.47 signifies the Master's attempt on the Doctor's TARDIS's Eye of Harmony, 47.99 signifies the Doctor's reunion with Sarah Jane Smith and Jo Grant at his supposed funeral, and so forth.

In my spreadsheet Cell B42, I always have a formula that cycles from the beginning to the present of this notation for the Doctor Who screen history as described in previous entries tagged math, with Excel cell formatting showing two significant digits. All the other cells with formulas tracking other shows' chronologies key on the formula in Cell B42. In my spreadsheet I manage to keep it mostly in a 3x3 cell grid, so some cell formulas track more than one show (sequentially usually, but sometimes concurrently), but I'm not going to combine show formulas for you here.

DOCTOR WHO SERIAL/EPISODE

=IF(OR(B42<1, OR(B42>=48, OR(AND(B42>27, B42<42), AND(B42<47, B42>=43)))),0,1)+MOD(B42,1)*IF(AND(B42>=8, B42<13),5,IF(OR(AND(B42>=5, B42<7), AND(B42>=18, B42<22)),7,IF(OR(AND(B42>=13, B42<18), INT(B42)=22),6,IF(OR(INT(B42)=2, INT(B42)=4),9,IF(INT(B42)=3,10,IF(INT(B42)=1,8,0))))))+MOD(B42,1)*IF(AND(B42<42, B42>=27),0,IF(OR(AND(B42>=23, B42<27), INT(B42)=7),4,IF(INT(B42)=46,5,IF(INT(B42)=42,13,IF(B42>=43,14,0)))))

For instance, for the figure 1.02 in Cell B42, this formula will return the figure 1.24, signifying the near end of Episode 1 of four of An Unearthly Child, the first serial of the first series; about the time the Doctor sets the TARDIS into flight with Barbara and Ian aboard. The figure 42.07 in Cell B42 returns 1.91, signifying most of the way through the first Season 2005 episode Rose, about the time Rose swings out over the vat of Intelligence.

Christmas specials are Episode 0 of the following season. Sarah Jane Adventures: The Wedding of Sarah Jane Smith is counted as Episode 2 of four (where both parts of The End of Time are counted as one episode), between Planet of the Dead and Waters of Mars, of Season 2009. Sarah Jane Adventures: Death of the Doctor is counted as Episode 14 of Season 2010.

DATE

For a very approximate air date from the Doctor Who figure in Cell B42 (the inversion of the Cell F4 formula),

=(B42-1)*365.25+23338.75
apply Excel cell date formatting

STAR TREK

For Star Trek [the original series] stardates (not including the latest movie),

=IF(OR(B42<1.875,B42>29.04),"",IF(B42>26.59,(B42-26.59)/2.45*1069+8454,IF(B42>24.01,(B42-24.01)/2.58*64+8390,IF(B42>21.59,(B42-21.59)/2.42*180+8210,IF(B42>19.59,(B42-19.59)/2*80+8130,IF(B42>=17.04,(B42-17.04)/2.55*518+7612,IF(B42>6.79,(B42-6.79)/10.25*1057+5943,(B42-3.79)/3*4631+1312)))))))
apply number formatting for rounding to one digit

(In regards the "" in the first IF statement - when a cell formula is inactive, I generally prefer a blank text-formatted entry rather than just a zero. Your mileage may vary.)

For Star Trek: The Next Generation stardates (also encompassing Star Trek: Deep Space Nine and Star Trek: Voyager)

=IF(AND(B42>24.84, B42<40),(B42-24.84)*1000+41000,"")
apply number formatting for rounding to one digit

For Star Trek: Enterprise dates: Though I'm not presenting the formulas here this way, in my spreadsheet I keep this formula as part of a formula in one of the stardate-formatted cells, even though Enterprise didn't use stardates. With this formula, to the left of the decimal is the year C.E. and to the right is a fraction of the year, starting at a date in April 2151 from the log entry in Broken Bow:

=IF(OR(B42<38.84, B42>42.84),"",(B42-38.84)+2151.29)

If you'd rather do Star Trek: Enterprise by date C.E. in a cell with date formatting:

=IF(OR(B42<38.84, B42>42.84),"",(B42-38.84)*365.25+DATE(2151,4,30))
with suggested date formatting mm/dd/yyyy

It's tricky trying to write a formula for the latest Star Trek. First of all, the new creative team changed the stardate algorithm: now the left of the decimal is the year C.E.; and the right is the number of the day in the year: .1 for January 1, .10 for January 10, .32 for February 1, .365 for December 31 (in a non-leap year) and so forth. Second, till their second movie comes out (currently scheduled May 2013) there's only one datapoint to plot from. So for the present all I do is take the stardate from the movie and treat it as I did Enterprise.

=IF(B42>46.455,(B42-46.455)+2258.42,"")
apply number formatting for rounding to one digit

FOR MOST OTHER SHOWS

With a few exceptions I sample below, most other shows are tracked just by season number. For instance, M*A*S*H:

=IF(AND(B42<22.3, B42>9.8),B42-8.8,"")

(The season count in this formula goes to the middle of thirteen, instead of only through eleven, because I include AfterM*A*S*H.) (Also, M*A*S*H could have an alternative formula in date format like Enterprise, with the dates of the Korean War, if you wanted; but I don't do it that way.)

Buffy the Vampire Slayer can't be expressed in a formula that simple because it was a midseason starter and its first season was short.

=IF(AND(B42<40.8,B42>=34.8),B42-32.8,IF(AND(B42<34.8,B42>=34.12),(B42-34.12)/0.68+1,""))

(Or, adjust the first argument of the first AND statement to B42<41.8 to include the last season of the concurrently running spinoff Angel.)

OTHER SPECIAL CASES
HITCHHIKER'S GUIDE TO THE GALAXY

My favorite version of Hitchhiker's is the tv version, it being the first I encountered, and I being a visual person. The six episodes aired over a five-week period in 1981. Now, I also wanted my formula to accurately represent that these characters continue to appear in cartoons set after the adventures chonicled in the program - but, in keeping with the spirit of the sourcework, only improbably crossing over with other characters. So that cell displays episode numbers during the run of the program, and for 11% of the time afterwards it displays the number 42.

=IF(AND((B42>18.24), MOD(B42,0.000001)>=0.00000089),42,IF(AND(B42>=18.12, B42<=18.23999),(B42-18.12)/0.02+1,""))

THE PRISONER

By episode number.

=IF(AND(B42>=4.78, B42<=5.22),(B42-4.78)/0.44*16+1,"")

STAR WARS

By episode number.

=IF(AND(B42<=45.49, B42>=36.5),(B42-36.5)/3+1,IF(AND(B42<=23.49, B42>=14.5),(B42-14.5)/3+4,""))

Or, including the Clone Wars tv show; bearing in mind a) those are set between Episode Two and Episode Three, and b) I don't know how long the series will last (though I think I read recently it was only going 100 episodes?),

=IF(B42>=46,(B42-46)/(B42-45)+2,IF(AND(B42<=45.49, B42>=36.5),(B42-36.5)/3+1,IF(AND(B42<=23.49, B42>=14.5),(B42-14.5)/3+4,"")))

BABYLON 5

By year set.

=IF(AND(B42>=30.8, B42<35.794),INT(B42-30.794+2258),"")

INDIANA JONES

The first three movies and The Young Indiana Jones Chronicles (I do recognize the fourth movie, but by then that cell has something else in it so I've never extended the formula that far) (updated for the fourth movie). By year set, which means it initially goes backwards.

=IF(B42>=45.55,TRUNC(1957+B42-45.55),IF(AND(B42<34.55, B42>=28.5),TRUNC(1938+B42-IF(AND(AND(B42<34.55, B42>=28.5), MOD(INT(B42*100),3)=0),58.5,48.5)),IF(AND(B42<34.55, B42>=21.5),TRUNC(1935+3*(B42-21.5)/4),IF(AND(B42<34.55, B42>=18.5),TRUNC(1936-(B42-18.5)/3),""))))

PLANET OF THE APES FILMS

By film number (not including revivals), with the tv show as film six.

=IF(AND(B42<=12.3, B42>=5.1),IF(B42>=11.8,6+(B42-11.8)*1.98,IF(B42>=10.1,(B42-10.1)/1.7+5,IF(B42>=8.1,3+B42-8.1,IF(B42>7.497,2,(B42-5.1)/2.397+1)))),"")

CEREBUS THE AARDVARK

By issue number.

=IF(AND(B42>=15.06, B42<=41.145),INT(IF(B42<16.06,(B42-15.06)*6+1,(B42-16.06)/25*293+7)),"")

That should be information enough for anyone who wants to use these formulas, but also wants one for a show that's not listed here, to work out how to write their own. Though of course I'm willing to answer questions.

Meanwhile, for anyone who is interested in the time tracking I do with the Doctor Who notation, as described in earlier entries tagged math: Currently my default is to divide a month into eleven Doctor incarnations. The formula in Cell B42 is the one previously described for dividing time into eleven equal parts, and prorating each part to each incarnation's screen history. This version assumes Cell A42 has a value between 0 and 1, Cell F4 contains the Doctor Who current year count as expressed in this essay's first formula, and an entry in Cell E5 that is the current number of incarnations.

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

I have a macro with a generic formula for Cell A42 for waking hours throughout the current month (where waking hours are tracked in Cell C2 as previously described), but copy-pasting that formula here breaks my HTML in a way that no other of these formulas does. So here's a quick and dirty formula specific to this month.

=($C$2-DATE(2011,12,1))/31
If you don't have a waking hours formula in Cell C2, for $C$2 substitute $A$1 or NOW().

So at December 2 22:00 (with waking hours of 07:00-23:00) the Doctor Who notation returned is 2.90, signifying about 13% of the way through the Season 1965 finale The Time Meddler, with an approximate airdate of 10/18/65, with an approximate stardate of -57.6*, with Get Smart and Hogan's Heroes about ten percent through their first seasons, and Bewitched and Gilligan's Island about ten percent through their second seasons.

* Yes, there are negative stardates in my fanfiction.

Comments

[theta-g.livejournal.com]

I'm very disappointed. It displays exactly what everyone says is your usual failure as an architect: not making allowances for people!

– Seventh Doctor, Paradise Towers

[scarfman]

Is this a criticism of the formulas? They were asked for. That was almost the first indication I've ever had that anyone even reads these...

[theta-g.livejournal.com]

To be fair, the quote I shared is not applicable to you. Just without a cut tag it -looks- like an visual assault on my friends list.

1. You crossover quite a large set of myths, which is relatively rare but it may open your work up to more people and deliver more people to each myth. Allowances for people, eh?

2. Your attempt to maintain a coordinated chronology for all those myths may well be one of a kind. It deserves praise for both the attempt and for leveraging a tool mainstream enough that your work may actually help someone else equally ambitious.

So well done, indeed :-)

[scarfman]

Just without a cut tag it -looks- like an visual assault on my friends list.

To have expressed myself precisely, I ought to have said, "Is that a criticism of the manner of presentation of the formulas?", to which I suppose your answer was yes. Sorry. I made a conscious decision when I started at LJ not to cut for length, and have only done it, as near as I recall, once; for an entry much longer than this.

[blinovitch.livejournal.com]

I am equal parts fascinated and baffled. Cell coordinates aside, what does this spreadsheet look like? Is a screenshot possible?

[scarfman]

Sure. Take me a day or so to put together a properly inclusive selection; it'll call for a new entry, I think, instead of just a reply to this comment.

[theta-g.livejournal.com]

Your conscious decision not to cut for length is yours to make :-)