The Arctic Joule Spreadsheet

New FAQs are added at the bottom of this page on a regular basis. Feel free to ask questions.

This page will be the home for the Arctic Joule calculations system, and there are FAQs now at the bottom. On the PDF, I’ve added some charts, but the spreadsheet data is still there in the background. Click on the chart below to get the full PDF. The front page graphic is available here full sized. Even the thumbnail below is kept current: you can see their progress, expected arrival, and the last update posted even before clicking on it:

FAQs: (Yes, there needed to be an FAQ system.)

Q: Why is the voyage longer than the planned 3,200km?
A: Because they are veering from the route somewhat by hugging the coastline. This adds to the expected distance, since a kilometer of actual travel is not taking them as directly toward their destination.

Q: What is the “veer” shown in the spreadsheet and on the charts?
A: That ties in to the voyage length. The great-circle distance (i.e., straight line across the Earth from point to point) was about 2,045km. But they can’t do that, and their planned route through the channels (a shortcut “Northwest Passage”) would be about 3,200km. This means that they would be going, on average, almost 1.6km along their planned route to get 1km toward Pond Inlet. The 1.6 number I’m calling the “veer ratio” or “veer” for short.

Q: Why is the expected veer different from the planned veer?
A: When the crew is forced to stay close to shore instead of heading straight across open water, they’re adding to the distance, and increasing the actual veer experienced. The spreadsheet uses this to calculate the rest of the trip, which is why the planned voyage extends beyond the original 3,200km. At one point it hit 4,000, as the crew of the Arctic Joule was forced to backtrack along their route by bad weather.

Q: How do you calculate the expected arrival?
A: Well, the formula is: =MLF_c_UT+MLF_c_TrackRemaining/MLF_c_AvgKpD) — in other words, I use the current voyage length (affected by how much they’re veering from course) to determine the “track remaining,” then divide that by the average kilometers per day they’re making to see when they might arrive. They can improve this date two ways. First, going faster helps, as it increases their average daily travel. Second, going more directly toward the destination (in other words, crossing open water instead of hugging the coast) helps a lot, as it improves the veer and reduces their overall planned voyage.

Q: Why are you doing this?
A: I’m interested in the trip for several reasons, and I found myself trying to calculate these things in my head. It was a bit tedious, and I am naturally inclined toward spreadsheets, so I built this for myself. I decided to give it a bit of decoration and make it available. There is no compensation involved from anyone. I wish the boys luck (and safety!), even if I disagree with some aspects of the motivation. The effort and attempt are inspirational enough.

New August 15, 2013:

Q: Where does this data come from?
A: The GPS pings from the boat are displayed on the www.MainstreamLastFirst.com website. They’re dropped after seven days; when I realized that, I started capturing all of them. So the first weeks are spotty (and they were pinging only infrequently then, skipping days sometimes).

Q: Why does your chart look like they cur across the land when leaving their starting point?
A: This was a case of them not using the GPS much, combined with my not recording these early pings. I hadn’t started this spreadsheet yet. I’ll put in an estimated point to make this look better. (Done.)

New August 18th, 2013:

Q: I still don’t quite get “veer”: Can you describe it more?
A: Yes: Veer is something expected for the trip. In order to get to Pond Inlet on the water (since they’re not flying) they can’t go in a straight line. Veer is the ratio of the route distance to the straight line distance, planned to be about 1.6 or so. However, our team has curved more along the coast, which so far has added about 15% to the veer ratio: It’s recently (August 17) at 1.8, roughly.

The logic to determine veer first spots their closest point to the planned route (using great circle distance remaining), figures out what the expected veer is (i.e., how far they should have gone to get 1km closer to Pond Inlet, usually around 1.6km), and compares this to the crew’s actual experience, which is more like 1.8 or so and is called “actual veer.” This comparison produces a “veer ratio,” which adds to their expected remaining voyage length. So, if they continue coast hugging, they’ve got a longer trip than if they’d have been able to follow the planned route.

The formula doesn’t help much, as it’s built up over several cells. Actual veer is easy enough:

=IF(MLF_curLon=Empty,Empty,MLF_c_Track/MLF_c_GCdist)

Expected veer is more complex:
=IF(MLF_curLon=Empty,Empty,INDEX(Rt_c_Veer,IF(MLF_curLon=Empty,Empty,MATCH(MLF_c_ToGo,Rt_c_Remain,MatchGT))))

Veer ratio is simply actual over expected. The “empty” business in front is just to blank the cells if no data is entered yet.

The “_c_” in the names refers to a column. The MLF_ table is the one you see, the RT_ table is the ideal route.

Q: Since they don’t provide a continuous track, and sometimes forget to turn it on thereby omitting some of the coast hugging, do you think that your veer computations might be slightly lower than what I’ll call the “real veer”?
A: You’re right, the veer is going to be slightly underestimated. But there’s a counter-effect: When they “forget” to turn on the pings as they experiment with a difficult run, this also means that their average speed does not benefit from their real track. If we knew every twist and turn they took, we’d have a higher average Km/Day rate — which means that once they point more toward the goal, they’d go faster than we think. The two would approximately balance out … or would be “veery close” so to speak.

Q: I noticed that the total GC distance (GrCirDist + GCToGo) has been slowing increasing. For the record dated 17-Aug 00:01:04 it’s up to 2,146 Km. What causes that?
A: The two great circle distances are from the Arctic Joule back to their starting point and to their destination. The total distance from Inuvik to Pond Inlet is about 2,045 km, so the only way to get that number as the total is to be exactly on the line between them. That hasn’t happened so far, as the GC route is rather north of them for most of the trip. (They’ll cross it on the final stretch a couple of times.)

So, since they’re south of the ideal air route, they’re on the point of a triangle with the Inuvik-Pond Inlet side forming the base. The two sides connecting to them will be longer than the base as long as the triangle has any altitude (in other words, as long as they’re not on a direct line). Does that make sense?

I use the GC remaining distance to figure out where they should be with regard to the planned route. There are about 240 plotted route points (contributed by my friend Marmoe), though the later segments are longer than the earlier ones as they have more open water. (Hah! More open last year, at least!) They’re now (Aug 17) near route point 128, and have been for the past day roughly. That allows me to determine how much they’re straying from their planned course by coast-hugging and whatever. Currently they’re running about 15% high (the actual-versus-expected veer ratio), which means the total trip distance gets bumped up by that 15%.

Since most of the distance is west to east, they’ve covered 33.9% of the “horizontal” longitude distance, but 37.5% of the total track distance based on my estimations.

Q: I don’t understand why the change in TRACK LEFT for a given day is greater than the distance traveled for a given day.
A: The Track Left is (1) reduced by how far they’ve traveled, but (2) changed (increased or reduced) by how much they are veering from the planned route.

You’re exactly right on the “inefficient path” aspect. Let’s say that the coast aligns or the water is good, and they make straight toward Pond Inlet. This reduces their veer, thus reducing the expected voyage, and thus the track left to cover of that voyage is also shorter. But if they’re going fast but heading sort of sideways, the track left is still reduced by the distance, but the voyage increases slightly as they are adding to the pattern of veering more than the planned course.

I am projecting the veer as following the pattern so far. It seemed reasonable (assuming an ideal path does not, to me), and would improve any time they change that pattern. As of now, they’re traveling 16% more distance than they planned (for the trip so far), so the voyage is projecting as 16% longer as a result. I hope they can change that, but it’s tough going up there. You can see how the veer compares on the second page of the chart section.

Recently, I started doing something different for “days behind” that helps their outlook: Previously, I’d calculated the days behind for their arrival — right now it’s five weeks, roughly. That looked scary, so I’m now taking only the percent of the voyage’s portion of it. So if they’re projecting 30 days behind at the end of the voyage, but they’re one-third through, I’m showing that as one-third of the thirty days or only ten days behind as of now.

New FAQs added August 22, 2013:

Q: Why did the “days behind” increase slightly, even when they’re making good time?A: There are two reasons. First, they need to hit that “kilometers per day needed” number every time (currently about 67km). Second, as of this writing they’re predicted (based on current numbers) to arrive about four weeks behind, if that were possible with the ice. So even if they lost no further time and did not change that arrival date, the “days behind” would creep up to eventually match the four weeks as they arrived.

I could have said (and did, early on) that they’re really 29 days behind because of the predicted arrival, but that seemed unduly harsh. So I’m using the status based on where they are now, about two-fifths of the way through the voyage. In fact, that’s the formula: currently 42% (of the voyage) of the 29 days behind producing the current 13 or so days.

Q: Why are the blog posts in the comments column a different color today?
A: I added this as a new feature. Point to one and click on it (or ctrl-click on some systems) and it will jump right to the MainstreamLastFirst blog entry named. It’s a convenient way to find your way back to blog posts from the crew you might have missed.

By coincidence, that “unidentified” ship (actually the Mango) is the freighter that transported Charles Hedrich’s similar-sized rowboat to Alaska for his run at the Northwest Passage crossing. And there’s a certain amount of controversy about what’s happened since:

Thank you for your hard work. The longer this Arctic farce goes on, the more entertaining I
find it to be. I’m really looking forward to their attempt to cross the D&U Strait.
I note (on Sailwx.info) that sailing ship “OS 8154” is working its way towards them – and is (apparently) the only vessel in the area that
could provide rescue.