Calculating your resting heart rate

As I've mentioned before, I've started tracking my daily resting heart rate.

(Not sure why it says weight.  It should say pulse.  I promise I don't weigh 61 pounds.)  So, first it's pretty erratic, but what I can see are tough workouts.  Like say, Saturday's race.  On Sunday, my resting heart rate was up to 59.  After 2 days it recovered (only to spike again today, I don't have a good rationale for that one).

But trickier than figuring out what the heart rate data means, is figuring out what my resting heart rate is.  I've taken to keeping my garmin next to my bed.  When the alarm goes off, I pull on the strap, start my watch, and snooze/check my phone for >5 minutes.  Then I upload this data to garmin connect.  It looks like this:

As you can see, it moves around quite a bit, and it always has a peak near the beginning as my heart rate recovers from putting the strap on.  I couldn't just use the average over the whole interval cause that peak would throw it off.  I had been eyeballing the average heart rate over what looked like the minimal minute.  But the engineer in me just couldn't cope with the inaccuracy.  I needed... a spreadsheet!

I've created a google spreadsheet, although the same functions work equally well in excel.  You're welcome to just use it to calculate your minimum heart rate, or you can read further where I'll explain how I created it.  

Either way, you'll download a TCX file from garmin.  Excel whines a bit and throws several errors about the data, but after accepting the risks and data formatting problems, it will open.  In the file, you'll see a column with the time you started the workout, the max heart rate, average heart rate, then observation times, heart rates at each observation.  Those last two are the only ones you need.

Those first two columns are directly from the TCX, just copy and paste.  If you're creating your own file in  excel, you'll need to paste as text, in the google spreadsheet you can just paste.  You're calculated minimum heart rate over a minute will display in cell J3.  

Now, if you're still with me, here's the fun stuff.  First, we need to get the time stamp out of the string in that first column.  We can do that using the =time() function and the =mid() function (which just takes characters out of a string).  I'm not worried about the the hours here, so I just set it to zero.

   

Next, I want to find the time that is one minute before the time stamp (this is the time I'll be looking for to determine how many rows to go back to average).

=D2-time(0,1,0)

Then I confirm that I have at least a minute of data to average by comparing the minute back time to the first time stamp in the set (don't forget the absolute reference otherwise bad things may happen).

=if(E3<$D$3,false, true)

Now for the magic.  You see, the garmin doesn't record obervations at regular intervals.  If it loses its connection to the heart rate monitor, then it doesn't record anything, so we can't just do a rolling average of say, the last 10 records.  We have to figure out how many observations were in that last minute.  We use the =match() function.  This finds the greatest value that is less than the sought value, so if we're looking for the minute before an observation at 2:59, match will look for the greatest value that is less than 1:59.  The result is the number of cells from the top of the field where that minute starts.

  

Now all we have to do is compute the average.  We use the =offset() function to compute the range of observations to average.  Offset takes an initial cell, shifts from that cell down a number of rows and columns, and then returns an array of the number of rows/columns specified, so we give offset the first heart rate value B3, shift by the index we just calculated, and then tell it the number of rows from that cell to the cell we started at.  

That was easy.  Now we have a column that gives the trailing average heart rate, we take the minimum of that column and we're good to go.