Daniels Tables Excel Worksheet v3.01.00

Can you switch between imperial/metric in this tool? I'm strictly km, never miles.

..... wrote:

Can you switch between imperial/metric in this tool? I'm strictly km, never miles.

Download the excel file, you can tick in the columns where it says metric.

Amazing work really.

darkwave wrote:

darkwave wrote:

Love this.

One question: I must be missing it, but I don't see where on this sheet we can mark ourselves as female.

NM - I figured it out. If you are female, go to cell C4 and type FALSE. That will change the sex.

[I should note that I imported this into Google Sheets, so it may be that how to change sex on the sheet is obvious in the original excel format, but lost when the sheet is imported into Google]

Yes, when importing into Google Sheets you lose some functionality. I don't use Google Sheets and I don't want to go through the effort of maintaining two versions. However, if you're handy with making edits, here are a few tips (worked out with the help of another GS user) for making the spreadsheet more GSheets friendly:

Note: The Google Sheet mods below apply to version 3-01-03.

1) The Custom Distance entry section uses radio buttons in cells S6-S7 which are not imported in GoogleSheets, to replace this functionality:

- Select Cells S6-S7

- Reset the Font so you can see the data in these cells as follows:

- Find the Text Color Icon near the middle of the row of formatting Icons across the top of the sheet, select Reset or choose a color.

- - - (also center the text and change font size if you like)

- In S6 manually enter a 1 to set your Custom Entry to miles, or a 2 for Km.

- If you want, in S7 enter the following formula: =IF(S6=1, "Miles", "Km"), to more clearly label the setting choice.

2) Excel Checkboxes are also lost in GS. However, you can select/de-select these options by manually typing True or False into the appropriate cells. While it's not necessary, you may choose to make the True/False entries visible by resetting or changing the font, as described above.

The following cells are associated with Checkboxes:

Personal Data:

B4 - True = Metric weight and height

C4 - True = Male

Intensity Table:

D16 - True = Metric

E16 - True = Double KM values

Various Pace and Projection Tables:

L47 - True = Metric

Q49 - True = Male (for age/sex grading comparisons)

L53 - True = Metric

L63 - True = Metric

H67 - True = Show HR Values (False = Show % of HRmax)

L69 - True = Metric

P70 - True = Metric (paces in Extended Projection Table)

L75 - True = Metric

L82 - True = Metric

R85 - True = Metric

B100 - True = Male

For Entry Time Grade Summary:

N96 - True = Add rather than subtract Temperature adjustment

M100 - True = Include Age Grading

M101 - True = Include Weight Adjustment

M102 - True = Include Temp Adjustment

To add some descriptive text to the Entry Time Grade Summary options:

- Select cell M100 and Reset Font color

- Find the Merge Cells option a couple Icons to the right of the Font Color icon

- Click the down arrow on the Merge Cells option and select Unmerge

- In cell N100, enter the text "Age".

- Follow the same procedure for cells M101 (Weight) and M102 (Temp)

If you want to go through the effort, you can insert GS checkboxes in some or all of the above fields as follows:

- Select the cell

- Reset Text Color (so you can see the checkbox)

- Data/Data Validation

- Criteria: Checkbox

- Check the "Use Custom Cell Values" option

- Checked = True; Unchecked = False

- Appearance: Select "Show Validation Help Text"

- Enter a appropriate short description for the field (Such as Checked = Male), which will pop up when the field is selected

- Save

3) The Excel file also has 10 or so macros which allow hiding of certain sections, resetting defaults and clearing entries. Google Sheets does not pull in Excel macros, buttons, etc, so you don't get any of that. The most significant space saver lost in G-Sheets is a macro button to hide rows 17-45. Since I never use the Intensity Points I keep these rows hidden (Ironic, since it was Intensity Points that initially launched this project). You can hide these rows manually if you don't expect to use them either.

To further tidy things up, you can also hide any rows between 8 and 15 you're not interested in seeing. Just make sure to hide them, don't delete them. Deleting them will cause issues.

I think these changes will allow you to use the worksheet in Google Sheets. Be aware that if you edit the file with Google Sheets online, it will also modify the master copy in your G-Drive on your local PC. I suggest creating a 2nd copy with a new name to work with in Google Sheets (for example, DanielsTables3-01-03-GS.xlsm).

Let me know how this works out for you.

Regards,

Herm

Thanks everyone for the kind remarks...

It is pretty amazing what Excel is capable of with significant persistence and a big enough hammer.

This Excel spreadsheet is awesome, thanks!

Could you tell how the "Weekly workload distribution" table is created? What is the foundation for all those formulas?

This.

??

andrew_bumetsov wrote:

Could you tell how the "Weekly workload distribution" table is created? What is the foundation for all those formulas?

Sorry, I don't get email notices when this thread is updated, and I haven't visited in quite sometime.

Are you asking about the percentage calculations? They just calculate what percentage of total mileage the weekly total for each row is.

wow! I love this spreadsheet...been using it for years. thank you very much. I have a question if you come back to the thread. There's a thing called "heart rate tweak." What is that about?

Thanks again. I use this thing all the time. Much appreciated.

Coach Jeff ROC wrote:

wow! I love this spreadsheet...been using it for years. thank you very much. I have a question if you come back to the thread. There's a thing called "heart rate tweak." What is that about?

Thanks again. I use this thing all the time. Much appreciated.

During races there is a heart rate "drift"(individual).

Personally, I have 5+-1 beats per 1h of race with same pace, if all weather, hydration, nutrition factors are perfect.

Example, If you will run a marathon, lets say 02:30:00, at your marathon average pulse 160, you have to consider that at the beginning of the race pulse will be 152 and at the end might be 167, meaning your heart rate drift is 15beats per 2.5 h

Canefis wrote:

Coach Jeff ROC wrote:

wow! I love this spreadsheet...been using it for years. thank you very much. I have a question if you come back to the thread. There's a thing called "heart rate tweak." What is that about?

Thanks again. I use this thing all the time. Much appreciated.

During races there is a heart rate "drift"(individual).

Personally, I have 5+-1 beats per 1h of race with same pace, if all weather, hydration, nutrition factors are perfect.

Example, If you will run a marathon, lets say 02:30:00, at your marathon average pulse 160, you have to consider that at the beginning of the race pulse will be 152 and at the end might be 167, meaning your heart rate drift is 15beats per 2.5 h

That would make sense. Thanks

Coach Jeff ROC wrote:

wow! I love this spreadsheet...been using it for years. thank you very much. I have a question if you come back to the thread. There's a thing called "heart rate tweak." What is that about?

Thanks again. I use this thing all the time. Much appreciated.

As someone who personally does not tend match the default J.Daniels HR profile very well (my racing HRs are lower than projected), The Heart Rate tweak is an ongoing attempt to allow runners to customize the calculated HR by bumping it up or down. and hopefully better align them with personal experience. I've never been fully satisfied with this functionality, especially as it relates to racing HR (still not), and over the years there have been various approaches at tweaking the relationships between, %VO2max, %HRmax and pace... some of them rather poorly implemented. The current version of the spreadsheet, in case you don't have it, is 3.05.02. It can be downloaded at

I also have a Facebook page where I notify users of updates and answer questions (although after a recent bout of modifications, nothing new is expected in the near future). I also occasionally post links to videos or articles by J.Daniels and others that happen to catch my attention, and sometimes share my own trials and tribulations, all of which are open to discussion:

Very much appreciated. I hear you on the HR values/paces lining up, but it makes sense that its a difficult thing to get right... maybe impossible to get exactly right. Thanks again, friend.

I imported into google sheets which works, though the imperial/metric checkboxes are not visible, but where the checkboxes should be, just change cell value to TRUE and it's good to go!

Oh, I can help you in that area. Once I had to transfer the data too, but I didn't know-how. I searched for information on various forums and Google. But a user under the nickname Hardy wrote to me, bro, watch this video https://www.youtube.com/watch?v=_kyYqC7yWZ8, and solve your problem in 10 minutes. So it was; I watched the video and did everything in almost 10 minutes. Hardy, if you are reading this, I am very grateful to you; I forgot my password on that forum and could not answer (

My turn to ask a question... J.Daniels (and others) relate percentage of VDOT/VO2max to race performance times (rather than distance). This makes sense to me. Since my all out 29 minute 5k takes more than twice as long as a good college runner. Obviously I have to go at a lower %V02max to last twice as long (greatly simplifying and ignoring some other important contributing factors).

For a 29:00 5k, JD's formula gives a %VDOT of 93.2%. The spreadsheet takes that %VO2max and converts it to an equivalent %HRmax. There are different theories on the relationship between %VO2max and %HRmax, but it's generally accepted there is a fairly linear relationship between the two. Using JD's guidance from the 2nd Edition (Table 2.2 pg 39), 93% of VDOT is equivalent to 96% of HRmax. Using my HRmax of 165, that works out to be 158.4 bpm.

What I'm not clear on is what portion of the race is that estimate of 158bpm proposed to apply to? In a 5k race, my HR doesn't flat line. It comes up hard over the first 5 minutes or so, then tapers off, but generally continues on a gradual incline towards the end of the race, sometimes with a bump at the end if I kick in.

In the chart below I'm around the 150-155 range from the half way point until a minute to go, where a hard (and unpleasant) kick bumps it to 162 (near my max).

My average HR for the entire race was 149, that's a long way from 158. It appears if I took an average of the 2nd half of the race, it would be pretty close to 155, a lot closer. This is a typical trend for me, so I've tended to believe the %VDOT calcs must apply to the last half of the race.

I know all of this math is just estimations, we're all different, conditions vary, and all that... but the estimations must be based on some fixed assumptions.

http://electricblues.com/html/5k%20Race%2006-12-2021.JPG

I don't have an answer, but a speculation... that the longer the race, the closer the HRavg will be to that expected HR. The distance from resting HR to racing HR is shorter and you'd be maintaining racing HR for a longer percentage of the race .

Yes, as the time gets longer, the HR trend would change. In a well paced marathon, my HR would level off early and stay relatively flat for much of the middle portion of the race, and as I remember this would be at a level closer to the prediction.