Monday, October 15, 2012

yogi_Format Minutes And Seconds Only As Hours And Minutes And Perform Coach Specified Computations

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #809  Oct 15, 2012

user CC Coach said: (!mydiscussions/docs/CvEx6-WCBzc)
I'm using Chrome on a Mac. 

I have a spreadsheet I'm using for all of my cross country athletes' data. I enter their race times and get their paces calculated. Now I'm trying to crunch some numbers but running into trouble. 

I'm working specifically with mile paces (everything is under 9:00/mile)

1. First of all, I wish I could use a m:ss format easily. But that format is not built-in right now. It's a little maddening, because it's easy to do in Excel and Numbers. I want paces to come out as 6:06. I really don't want 06:06 and definitely not 0:06:00 or 6:06:00. For now, I'm using a time format (15:59 choice) and it works. 

2. My biggest problem is I want to do some calculations and projections based on difficulty of course. I'm trying to take a bunch of paces for lots of runners and do things like add 8 seconds to get a projection for another course. I get a parse error if I do something like d6+:08. It will work if I format all cells as plain text and enter my times like 6.06 and then use my formula as d6+.08. But then I have to retype all the times I copied from my other sheets since they are all in 6:06 format. 

Any help is greatly appreciated.
Thanks for the offer. I have all the data in there. I just took out the guys' names:

I put a note in one of the cells telling you what I'd like to accomplish. 
following is a solution using array formulas for entire set of values in a column ... single formula for the minimum times in range O20:O29 although simple but is very very long