Excel Tips And Tricks For Your Log

[[old] Jim Barry]

I posted this to the FAQ section but thought I'd give it some airplay in the regular forum too. <br /><br /><br />Excel tips:<br /><br />Just thought I'd share a piece of my rowing log for those that log their sessions in Excel. Add to this FAQ (in other forum section) if you have some more. <br /><br /><br />To have the spreadsheet do the 500m average pace automatically: <br />Put the distance in the A column. The minutes in the B column. The seconds and tenths of seconds in the C column. Then in the D column put this:<br /><b>=(B1*60+C1)/(A1/500)/86400 </b><br /><br />Format the cell with custom type mm:ss.0 (may have to type this format in manually if it is not available from the custom selections "menu").<br /><br /><br />For watts it's <b>= (A1/(B1*60+C1))^3*2.79 </b>(put that in column E)<br /><br />If you put the Stroke rate in column F then you can get Watts/Stroke Rate Index (aka SPI) as easily as <b>=E1/F1</b><br /><br />Meters Per Stroke is <b>=A1/(B1+(C1/60))/F1 </b><br /><br /><br /><br />And finally, the average airspeed of an unlaiden sparrow put in Bird's distance (meters) in column G, the time (in seconds) it took in column H and the formula as <b>=G1/H1 </b> <br /><br /><br />

[[old] Citroen]

<!--QuoteBegin-Jim Barry+Dec 14 2005, 09:24 PM--><table border='0' align='center' width='95%' cellpadding='3' cellspacing='1'><tr><td><div class='genmed'><b>QUOTE(Jim Barry @ Dec 14 2005, 09:24 PM)</b></div></td></tr><tr><td class='quote'><!--QuoteEBegin-->And finally, the average airspeed of an unlaiden sparrow put in Bird's distance (meters) in column G, the time (in seconds) it took in column H and the formula as <b>=G1/H1  </b>  <br /> </td></tr></table><br /><br />Is that the African sparrow or the European sparrow?

[[old] johnnybike]

Thank you for contribution Jim<br /><br />John

[[old] Dickie]

<!--QuoteBegin-Jim Barry+Dec 14 2005, 05:24 PM--><table border='0' align='center' width='95%' cellpadding='3' cellspacing='1'><tr><td><div class='genmed'><b>QUOTE(Jim Barry @ Dec 14 2005, 05:24 PM)</b></div></td></tr><tr><td class='quote'><!--QuoteEBegin-->I posted this to the FAQ section but thought I'd give it some airplay in the regular forum too. <br /><br /><br />Excel tips:<br /><br />Just thought I'd share a piece of my rowing log for those that log their sessions in Excel. Add to this FAQ (in other forum section) if you have some more. <br /><br /><br />To have the spreadsheet do the 500m average pace automatically: <br />Put the distance in the A column. The minutes in the B column. The seconds and tenths of seconds in the C column. Then in the D column put this:<br /><b>=(B1*60+C1)/(A1/500)/86400 </b><br /><br />Format the cell with custom type mm:ss.0 (may have to type this format in manually if it is not available from the custom selections "menu").<br /><br /><br />For watts it's <b>= (A1/(B1*60+C1))^3*2.79 </b>(put that in column E)<br /><br />If you put the Stroke rate in column F then you can get Watts/Stroke Rate Index (aka SPI) as easily as <b>=E1/F1</b><br /><br />Meters Per Stroke is <b>=A1/(B1+(C1/60))/F1 </b><br /><br /><br /><br />And finally, the average airspeed of an unlaiden sparrow put in Bird's distance (meters) in column G, the time (in seconds) it took in column H and the formula as <b>=G1/H1  </b>  <br /> </td></tr></table><br /><br />To calc the 500 meter average in excel put the time in cell A using the custom type hh:mm:ss.0 put the distance in cell B and the formula (a/(b/500)) in cell C with cell C having the custom format mm:ss.0.<br /><br />If you want to keep running totals, you can sum time columns using the custom format dd:hh:mm:ss.0, but the time rolls over to 00:00:00:00.0 after 31:23:59:59.9.<br /><br />