rise of the planet of fandom with math

[scarfman]

blinovitch, who inspired the most recent entry tagged math by asking for one describing formulas for other shows (which I already had been feeling badly about, having promised it last year and not delivered it), replied to that entry by asking for screenshots of my actual spreadsheet.

But first one note on that entry from the other day: since then I've updated the formula in my spreadsheet for Indiana Jones to include the fourth movie and the prospect of a fifth (since Spielberg says he and Lucas have agreed on a macguffin). Yesterday I edited the previous entry with the updated Indy formula, but I also resolved to reprint the updated formula in the next math-tagged entry, in case an interested party may have missed it. So here we are.

=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),""))))

So, about these screen captures. First thing - there'll be a few samples from different periods in Doctor Who's history, and I'll describe each cell of each screenshot in some detail to show how I use the formulas in my spreadsheet. Description is required, since I've been playing with this layout so long that I don't need labels.

Second thing - as I said in the last math entry, I combine the formulas I described there several into the same cell, so that I can keep them all in the same 3x3 grid. Well, I say 3x3, but the bottom row cells are so doubled up that the resulting combination formula has too many nested IFs for a single Excel cell, and the visible cell is obliged to call on the cell below it which is formatted invisible. One day I suppose I'll be tracking enough shows at once that I'll have to go 3x4. But that day is not today.

Third thing - for advanced or at least intermediate Excel users (if we aren't past that point already): Note that all the formulas I gave you last time referred to Cell B42 as a relative cell address (no dollar signs signifying absolute cell addresses in the formulas). This means, if you construct a grid like I've done, you can copy and paste it anywhere else in your spreadsheet and the relative position of the Doctor Who notation cell on which all the others are dependent will remain the same to the copied cells in the rest of the copied grid. I do that all the time; that's how the grid I use for everyday comes to be at Cell B42 instead of at the top of the spreadsheet.

So, here's the 3x3 status at the time of the entry of Star Trek into my fanfiction chronology with The Cage.

At station zero, the left cell in the top row, is the Doctor Who notation, 1.88, indicating progress of .88 though Doctor Who's first season (actually it's 1.875 rounded by two-decimal-place cell formatting). That would be right at the beginning of the final story, Reign of Terror, of eight of that season. You'll see I make the two decimal place cell formatting pretty standard for shows tracked by seasons/years/episodes.

In station one, the second cell in the top row, is displayed the formula for Star Trek original series stardates. This formula counts from 1312 (Where No Man Has Gone Before) for the Doctor Who notation 3.79 to 5943 (Turnabout Intruder) for the Doctor Who notation 6.79; when you project that formula backwards to the approximate production date of The Cage (which doesn't have a stardate), you get a negative stardate; hence, negative stardates in my fanfiction for the Christopher Pike era. Displaying stardates, the cell is of course cell-formatted for one decimal place.

Station two, the right cell in the top row, is blank at this time. None of the shows tracked by the combination formula in that cell were airing at this point in the chronology. If station zero is less than 1.875, station one is blank.

Station three, the left cell in the middle row, holds the approximate airdate formula. Since Superman and other superhero comics are always set in an eternal now, station three represents those heroes to me. This formula's cell may be kept in a variety of date/time formatting, which will be demonstrated in these screenshots. This one shows straight date.

Station four, the middle cell of the middle row, is blank right now. Since the first show I ever put in station four was M*A*S*H, the show that got me through adolescence, I reserve station four for special favorites.

Station five, the right cell of the middle row, is blank.

Station six, the right cell of the middle row, displays Bewitched season at this point in the chronology. Bewitched has only just come on, so it's .08 through its first season.

Station six, the left cell in the bottom row, is blank.

Station seven, the middle cell of the bottom row, displays Gilligan's Island season at this point. That show also has only just come on.

Station eight, the right cell in the bottom row, is blank.

Here's the first time, with the formulas presently in my grid, that there's something in every cell of the grid.

At station zero in this screenshot we have 4.78, indicating late fourth season for Doctor Who, Patrick Troughton's first year.

Station one shows stardate 2840.2, about the stardate of The Galileo 7. Note: my stardate formula makes absolutely no attempt to correlate an episode's stardate with episode's actual airdate in the "approximate airdate" displayed in station three; as stated above, the formula only provides a linear progression from 1312.0 for the air date of the series premiere to 5943.0 for the series finale.

Station two is now displaying The Prisoner by weekly episode number, the first of seventeen, first time station two's had anything in it.

Station three has the date, same cell formatting as earlier, still smack in the middle of the Mort Weisinger era of Superman comics, Mr Mxyzptlk and red Kryptonite.

Station four shows Get Smart nearing its third season premiere.

Station five shows Bewitched nearing its fourth season premiere.

Station six shows Hogan's Heroes nearing its third season premiere.

Station seven shows Gilligan's Island nearing its series finale after three seasons; shortly the grid will have an empty cell again. But the first Planet of the Apes movie opens shortly before The Prisoner leaves the air, so the grid'll be full for another short while then. (Not every show I track with my grid will appear in thse examples.)

In station eight Steven Hill is about to turn over the lead role in Mission Impossible to Peter Graves.

In this screenshot station zero shows 13.41, during Tom Baker's second year as the Doctor.

In station one we have an approximate stardate for the unscreened later years of the starship Enterprise's five-year mission. This formula provides a linear progression from 5943.0 for the series' finale airdate to 7000.0 just before the opening of the first movie.

Station two at this time is tracking The Mary Tyler Moore Show and its spinoffs. Mary Richards is in her sixth year in Minneapolis. Rhoda is in its second season. Phyllis is in its first. Lou Grant will come along in a year and a half.

Station three shows the date and time of the premiere of my first daily cartoon with tv characters in it, smack in the middle of the Julius Schwartz era of Superman comics, GBS News and Steve Lombard. This screenshot has the cell in date and time formatting.

Station four shows the fourth season of M*A*S*H, the first year of B.J. and Colonel Potter. Hawkeye and B.J. were in that cartoon.

Stations five, six and seven are blank.

Station eight shows the second season of Barney Miller, their first full season.

In this screenshot's station zero we have 24.88, signifying late in Sylvester McCoy's first year as the Doctor.

Station one shows stardate 8411.6, signifying a time between The Voyage Home and The Final Frontier.

Station three has the stardate 41040.0, signifying early in the first year of the mission of the Enterprise-D. Since the original series movies and The Next Generation overlapped, I built the formula for the TNG stardate in station two. As you'll see some of below, after The Undiscovered Country, TNG gets to move into station one ... but shortly afterwards, even though it's the same figure, Deep Space Nine stardates appear in station two; and stations one and two have formulas returning identical figures identically formatted from the debut of Deep Space Nine (when TNG is station one and DS9 is station two) till its finale (when DS9 is station one and Voyager is station two.) (After DS9 leaves the air Voyager moves into station one.) These formulas were written early in the grid's history. Later on we'll see how I came up with other paradigms for series sharing cells.

Station three shows October 10, 1987, shortly after the Crisis on Infinite Earths.

Station four is blank.

Station five displays early in the first year of Beauty and the Beast.

Station six shows an approcimate year in Indiana Jones continuity. However, this was also the cell where, before Indiana Jones appeared in it, the six week run of the tv Hitchhiker's Guide to the Galaxy is tracked. As the figure in station zero progresses, the formula in station six flashes 42 11% of the time, signifying an improbable chance of crossover with HHGG.

Station seven is blank.

Station eight shows issue 110 of Cerebus the Aardvark.

The next two screenshots - in conjunction with the accompanying descriptions - better illustrate the doubling-up of shows into single cells touched on in the description accompanying the previous screenshot.

Station zero diaplays 33.47, signifying about halfway through Doctor Who's thirty third year - when Paul McGann played the Doctor for his only time on tv.

Station one and station two show the same 24th century stardate, 49634.0 - midway through DS9's fourth season and Voyager's second.

Station three shows May 14, 1996, the date the Paul McGann Doctor Who movie aired; shortly after Superman's death and resurrection, shortly before Grant Morrison's revival of the original Justice League.

Station four is blank (though Buffy the Vampire Slayer will show up there in less than a year).

Station five shows 3.68, most of the way through the final season of Forever Knight.

Station six shows approximate calander year for Indiana Jones - more specifically, for The Young Indiana Jones Chronicles, which has been on for about five years now, if not exactly continuously. (But wait, you may say, that show was about Indy at about twenty and at about ten. Patience.)

Station seven shows 3.68 for The X-Files. But the same figure can be referred to for seasons of, e.g., seaQuest DSV, until it's 4 or greater; or Lois & Clark (though technically all permutations of that franchise're covered by station three); or add one for Highander or Hercules: The Legendary Journeys; or subtract one for Xena: Warrior Princess.

Station eight shows Cerebus issue number.

Now for a screen capture for about a week later in continuity time:

Station zero is at 33.49, only a little further along in the Doctor's timeline.

Stations one and two are still DS9 and Voyager.

Station three is almost a week later than the last screenshot.

Station four is still blank.

Station five is still late in Forever Knight.

Station six is still showing a calendar year for Young Indy - but the formula alternates between Younger Indy and Youngest Indy. The last screenshot showed 1922 and now we have 1912.

Station seven still covers all the shows listed for it in the previous screenshot description.

But station eight also has two stories sharing it at this point: aside from Cerebus, half the time it displays a calendar year for Babylon 5.

The final two screenshots include the row above the 3x3 grid because, while the formulas I keep there don't directly affect chronologies in the grid, they do have some bearing on my fanfiction.

Cell B41 in this screenshot contains a formula for tracking King Arthur in Time and Space or Arthur, King of Time and Space spacedates. The convention for this notation is, it starts from 10.0 (one decimal place) at the beginning of the first year of Arthur's reign as High King of British Space, and progresses ten for each year of his reign. Here we see, when AKOTAS comes back from its sketch hiatus, we'll be a little better than halfway through the twentieth year of Arthur's reign.

Cell C41 at a given time contains one of several formulas, even random, for displaying an integer value between 0 and 9 inclusive. When I'm generating a crossover at random, this cell tells me the heroes of which other show the Doctor is visiting, where the integer signifies the station in the grid with the chronology of the show. If it's 0 or a station is indicated that's blank, such as station seven is in this screenshot, then the Doctor is in his own universe. If the integer is 9, the Doctor is crossing his own past timeline, and then the formula also returns a two-digit figure to the right of the decimal to indicate what past year of Doctor Who he's crossing over to. Currently showing 1, it signifies a Star Trek crossover.

Cell D41 contains the formula I printed last time for serial/episode count for individual Doctor Who seasons. Currently indicating .82 through episode 0 of the season of year 49, it signifies The Doctor, the Witch and the Wardrobe.

Station zero shows 49.06.

Station one shows 2261.0, my best calculation as to a current stardate in the latest Star Trek continuity.

Station two shows 5.3, signifying partway through the coming fifth season of EUReKA. (I sometimes use the station two EUReKA formula for Torchwood and Sarah Jane Adventures as well, since they all started up sort of approximately kind of the same time.)

Station three displays just about the exact moment I will turn fifty two years old. It also signifies the Superman comics' universe "New 52" continuity, when evereyone gets new uniforms and origins and costumes and spinal chords and things.

Station four shows that, had it remained continuously on the air, Buffy the Vampire Slayer would be .26 through its 16th year.

Station five shows approximately how far along Star Wars: The Clone Wars has depicted the events between Attack of the Clones and Revenge of the Sith.

Station six shows an estimated calendar year in Indiana Jones continuity.

Station seven is blank.

Station eight signifies the fourth season of Fringe or of Sanctuary, or add one for Leverage, or subtract one for Warehouse 13.

No significant change in Cell B41.

Cell C41 is now displaying 6, signifying the Doctor is visiting the heroes whose chronology is tracked there, where we'll get in due course.

No significant change in Cell D41 either; nor in stations zero, one, two, or three.

In station four, we no longer have a figure for BVS because, for the time since Angel left the air, the formula only places a figure there for the BVS/A universe half the time.

No significant change for station five.

Station six also has a formula that at this point varies half the time (staggered so that crossovers with BVS are possible both for Indiana Jones and for the chronology noted here). Currently the formula at station seven indicates the first season of either Terra Nova or Once Upon a Time.

No significant change for stations seven or eight.

So that's my own implementation of the stuff I've been talking about. I'll answer any questions anyone may have, of course, but I think that pretty much finally covers the topic in all aspects.

Comments

[blinovitch.livejournal.com]

I understand it better now. Thank you for taking the time to do that.

So a lot of the process is still contained in your head, in that you yourself know what any given cell means and the points in time it represents, but the occasional passerby doesn't have all the information to decode it.

When you first mentioned the spreadsheet way, way back, maybe on adwc, my brain invented a complex grid of overlapping colored bars. Must be my visual thinking bias.

[scarfman]

Yes, yes, that's something I meant to mention in the essay (and will probably go back and add now); I've been playing in my grid so long I don't need labels.