Couple of Excel Questions...

[acerun007]

A little background. I'm a track coach and I'm currently importing the last couple years' stats into Excel to analyze. With that said, I have two questions right now:

Can I format the cells to read a distance?

For example, if I have 4', 5', 110', and 13', it will put them in order of 110', 13', 4', 5' in ascending order and vice versa for descending. I'd obviously want them to go 110', 13', 5', 4'.

My second question is a little more advanced, and may more easily be understood if you open my file. Basically, I want an individual athlete's tab to have his personal bests in each event, but I was hoping excel could sift through my data to find these values without me having to manually search for them. Excuse any mistakes in the file, I'm not quite finished with it yet.

Thanks for any help!

[Paul]

Instead of entering 4', 5', 13' and 110' into the distance cells, just enter the number and use a custom format on those cells to show the ' mark. This way your cells will still contain numeric data which can be sorted normally (Asc/Desc).

Select your distance cells (or whole column) and choose Format-->Cells...
On the Number tab, scroll down the Category list to 'Custom'. Select that, and in the 'Type' box, enter: #'

Click OK to close the Format window.

Now, when you enter numbers into cells in that column or range, they'll appear with a ' next to them, but they're still numbers instead of text.

[acerun007]

What if one of my distances is say, 23' 7.25"? Do I have to convert all of these to fractions? So then my 7.25 would equal .60416?

[Paul]

Thanks to Mr. Excel for these very useful distance functions. I added some notes in the attached spreadsheet on how to use and sort your data.

Hope it helps!