Excel Mathematics

[[old] John Hendrie]

Can anyone out there tell me how to add minutes and seconds in Excel? I imagine it requires a different kind of entry but I can't find it in Excel HELP. Stated differently, for years I have been entering my times in two columns (minutes and seconds). To compute my total time rowed I simply add (hrs+min/60+sec/3600) and come up with total time in hours (eg, I have rowed 1370.71 hours in 10 years) - quite simple. However, I have created a new spread sheet to tabulate my intervals and want to add the times (I know the PM3 does this for me but I love these academic challenges). As an example, I recently did 5x1500 in 6:16.8, 6:11.1, 6:11.6, 6:08.7, 6:06.1 (Iknow - pathetic but I am 70 years old you know) and want to add those for a total.<br /><br />Sorry for the long winded question. Hopefully the answer will be shorter.<br /><br />Thank you, john

[[old] sjmotzny]

<!--QuoteBegin-John Hendrie+Jan 24 2006, 10:42 PM--><table border='0' align='center' width='95%' cellpadding='3' cellspacing='1'><tr><td><div class='genmed'><b>QUOTE(John Hendrie @ Jan 24 2006, 10:42 PM)</b></div></td></tr><tr><td class='quote'><!--QuoteEBegin-->Can anyone out there tell me how to add minutes and seconds in Excel? I imagine it requires a different kind of entry but I can't find it in Excel HELP. Stated differently, for years I have been entering my times in two columns (minutes and seconds). To compute my total time rowed I simply add (hrs+min/60+sec/3600) and come up with total time in hours (eg, I have rowed 1370.71 hours in 10 years) - quite simple. However, I have created a new spread sheet to tabulate my intervals and want to add the times (I know the PM3 does this for me but I love these academic challenges). As an example, I recently did 5x1500 in 6:16.8, 6:11.1, 6:11.6, 6:08.7, 6:06.1 (Iknow - pathetic but I am 70 years old you know) and want to add those for a total.<br /><br />Sorry for the long winded question. Hopefully the answer will be shorter.<br /><br />Thank you, john <br /> </td></tr></table><br /><br />You should be able to simply "sum" the cells containing the times. For example,<br />suppose your interval times are in cells B1, B2, B3, B4, and B5. Suppose you<br />want the sum to be in cell B6. In cell B6, you should type "=SUM(B1:B5)" <br />(without the quote marks, of course, but the = is important). It's possible that<br />the result won't be in the right format. If not, click on the cell with your result<br />(B6 in this case), and then go to the Format:Cells... menu. Choose "Custom" in<br />the Category column. Then select a format such as mm:ss.0, or, if you expect<br />your sum to get into hours, try [h]:mm:ss.0.<br /><br />Hope this helps! Good luck.<br /><br />Steve<br />

[[old] jamesg]

When I want to record times in minutes I write in a single cell (for say 35:20) +35+20/60 enter. For the case you describe I'd write +5*6+(16.8+whatever)/60.<br /><br />Excel expresses the calculated amount as above in decimals (if you so wish) and entries can be summed using Sigma if in columns or rows; they're just ordinary numbers whatever the format they're expressed in. <br /><br />Formats are another problem. The time unit in Excel is the day. So if you want to see 02:00,0 written just like that, one way is to write it in seconds divided by 3600 and 24, and then apply the mm:ss,0 format to the cell, column or row. Minutes/60/24 will give the same result. Excel also lets you create your own formats.

[[old] gooseflight]

Excel is very good at calculating times. If you want to add times in minutes and seconds just enter them in the required format, e.g. 01:50.0 (mm:ss.0). You can then add, subtract, multiply, average etc. without further ado. You don't have to apply a format to the cells as Excel assumes it's a time format from the way it's entered.<br /><br />I have posted a sample file at <a href='http://www.machars.net/times.xls' target='_blank'>http://www.machars.net/times.xls</a>

[[old] Dickie]

The easiest way to handle times in cells is to format the cells using the 'Custom" option. When done this way you can perform calculations on the cell as you would on any other numeric cell.<br /><br />You can perform the following on a single cell, groups of cells or entire rows and columns.<br /><br />Select the cells you want to format for time, right click on the cells and select 'Format Cells' or select 'Cells' from the format drop down menu. In the 'Number' tab select 'custom' in the 'catagories' box, it is the last selection in the box. From there you can select among the various time formats. The only one that applies to us is mm:ss.0, however, you can create your own custom time formats simply by entering your format in the little box labelled 'type:'. I currently use the following custom formats on my excel spreadsheet;<br /><br />mm:ss.0 - for my split times for the piece<br />mm:ss.00 - for monthly split times<br />mm:ss.000 - for aggregate split<br />h:mm:ss.0 - to enter my piece times<br />d:hh:mm:ss.0 - for my monthly totals<br />dd:hh:mm:ss.0 - for my aggregate total<br /><br />There are some caveats;<br /><br />1. when you enter time always enter with the tenths of a second even if it is 0 tenths<br />2. the dd:hh:mm:ss.0 format rolls over to 00:00:00:00.0 after 31:23:59:59.9<br /><br />You can calculate split time easily with the following formula<br /><br />split cell = (time cell / (distance cell / 500))<br /><br />Where split cell and time cell have time formats (such as h:mm:ss.0) and distance cell is formatted as 'number'<br /><br />If anyone is interested in having an empty spreadsheet, by empty, I mean one where I have zeroed out my data but retained all other formatting and functions, then use the ranking email system to contact me. If you select the 500m list and limit your search to the state of NH, I will be on the first page.<br /><br />

[[old] John Hendrie]

Thank you fellas. I used the mm:ss.0 format and it works perfectly for suming the interval splits. I knew it had something to do with formatting but could not figure it out. Thanx again<br /><br />john