Advent of Code 2015 - Day 14: Reindeer Olympics

Part 1

Description

This year is the Reindeer Olympics! Reindeer can fly at high speeds, but must rest occasionally to recover their energy. Santa would like to know which of his reindeer is fastest, and so he has them race.
Reindeer can only either be flying (always at their top speed) or resting (not moving at all), and always spend whole seconds in either state.
For example, suppose you have the following Reindeer:

• Comet can fly 14 km/s for 10 seconds, but then must rest for 127 seconds.
• Dancer can fly 16 km/s for 11 seconds, but then must rest for 162 seconds.

After one second, Comet has gone 14 km, while Dancer has gone 16 km. After ten seconds, Comet has gone 140 km, while Dancer has gone 160 km. On the eleventh second, Comet begins resting (staying at 140 km), and Dancer continues on for a total distance of 176 km. On the 12th second, both reindeer are resting. They continue to rest until the 138th second, when Comet flies for another ten seconds. On the 174th second, Dancer flies for another 11 seconds.
In this example, after the 1000th second, both reindeer are resting, and Comet is in the lead at 1120 km (poor Dancer has only gotten 1056 km by that point). So, in this situation, Comet would win (if the race ended at 1000 seconds).
Given the descriptions of each reindeer (in your puzzle input), after exactly 2503 seconds, what distance has the winning reindeer traveled?

Input

Solution

Here is my attempt to solve this problem.

As you can see, I split the input into for columns: name, speed, bursting time, and resting time. For counting the distance these reindeer covered in the 2503^th seconds (cell E2), I use this formula:

=IF(MOD($E$2,D3+C3)>C3,(1+ROUNDDOWN($E$2/(C3+D3),0))
*B3*C3,ROUNDDOWN($E$2/(C3+D3),0)*B3*C3+MOD($E$2,D3+C3)*B3)

The code determine whether those reindeer are resting or bursting. If the time (2503) mod the time needed for the reindeer to burst again (bursting time + resting time) is greater than burst time, then the reindeer is resting. Then the code calculate the distance covered. and simply get the largest value of those distances.

Part 2

Description

Seeing how reindeer move in bursts, Santa decides he's not pleased with the old scoring system.
Instead, at the end of each second, he awards one point to the reindeer currently in the lead. (If there are multiple reindeer tied for the lead, they each get one point.) He keeps the traditional 2503 second time limit, of course, as doing otherwise would be entirely ridiculous.
Given the example reindeer from above, after the first second, Dancer is in the lead and gets one point. He stays in the lead until several seconds into Comet's second burst: after the 140th second, Comet pulls into the lead and gets his first point. Of course, since Dancer had been in the lead for the 139 seconds before that, he has accumulated 139 points by the 140th second.
After the 1000th second, Dancer has accumulated 689 points, while poor Comet, our old champion, only has 312. So, with the new scoring system, Dancer would win (if the race ended at 1000 seconds).
Again given the descriptions of each reindeer (in your puzzle input), after exactly 2503 seconds, how many points does the winning reindeer have?

Solution

For the part 2, I have to calculate the distance each reindeer covered in every second up to the 2503^th and determine which one is leading. Now in column G, I put an array formula to counts how many those reindeer are in the lead, and therefore the point they get. And finally find the largest of those numbers. Here is my array formula:

=SUM(IF(H3:CRN3=H$13:CRN$13,1,0))