I am a H.S. Track coach in charge of throwing events. I am trying to put together a spreadsheet that will average my athlete's results throughout the season to aid in predicting/estimating expected results prior to meets with the other event coaches.

Can anyone help me with: a way to have cells display feet and inches like this: (45' 3" or 127' 11") and then a way to have the results averaged out in the same format.

I found a custom format 0"'".00"''" that displays regular results nicely but returns an inaccurate/funky result when I use the =AVERAGE function for the cells containing the results.

For instance, I would like to have a list of shot put results for an athlete display like so:

30' 2"
30' 7"
29'11"

and have the average display as 30' 2"

I'm mainly concerned with the average distance being calculated accurately over the course of the season.