Averaging feet and inches (not decimal feet)

[Cory Boyd]

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

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

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

For instance, I would like to have a list of shot put results for a
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 calculate
accurately over the course of the season

 

[Spencer101]

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

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

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

For instance, I would like to have a list of shot put results for a
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 calculate
accurately over the course of the season.

Hi Cory,

Any chance you could post an example workbook showing the AVERAGE no
working as you'd expect?

I've tried putting together something similar and it works for me so I'
like to see where/how yours is going wrong

 

[Cory Boyd]

Hi Cory,

Any chance you could post an example workbook showing the AVERAGE no
working as you'd expect?

I've tried putting together something similar and it works for me so I'
like to see where/how yours is going wrong.

Thanks for responding! Here is the sample data I entered using th
custom format 0"'".00"''" (in cells D27):

22'.07''
23'.01''
21'.11''
22'.05''
23'.05''
24'.08''

in cell D12, I have the formula =AVERAGE(D210) and it returns th
result 22'.56", which I can't wrap my head around. I have trie
adjusting the formula cell range to D27 and it still returns the sam
result.

Thanks

 

[joeu2004]

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 [....]
30' 2"
30' 7"
29'11"
and have the average display as 30' 2"

Presumably you are entering the data in the form f.ii, where "f" is feet and
"ii" is inches (.01 = 1, .11 = 11). That is a difficult form to work with
in arithmetic formulas. It is doable; but difficult. See footnote [1]
below.

Instead, I would suggest that you enter the data in the form:

30 2/12
30 7/12
29 11/12

formatted as Custom "# ??/12" without quotes.

Despite the appearance, Excel actually stores the values as decimal feet.

So then you can perform any arithmetic in the normal manner, being careful
to choose the same Custom format for all cells.

If you insist on seeing 30' 12", for example, I would put the following
formula in a parallel cell, which is used only for display purposes (no
arithmetic):

=INT(A1)&CHAR(39)&" "&INT(12*MOD(A1,1))&CHAR(34)

formatted with Horizontal Right alignment.

Actually, to avoid computational anomalies that arise with Excel (native
computer) arithmetic with non-integers, the following is more reliable:

=INT(ROUND(A1*12,0)/12)&CHAR(39)&" "&MOD(ROUND(A1*12,0),12)&CHAR(34)

You can replace CHAR(39)&" " with "' ". And you can replace CHAR(34) with
"""". I just think that CHAR(39) and CHAR(34) is more readable in all
fonts.


-----
[1] If you insist on entering data in the form f.ii, or if you merely want
to convert existing data into decimal feet which you can format as Custom "#
??/12" without quotes, the following formula does the proper conversion:

INT(A1)+MOD(A1,1)/12

So you could use the following array-entered formula (press ctrl+shift+Enter
instead of just Enter) to average data in the form f.ii:

=AVERAGE(INT(A1:A100)+MOD(A1:A100,1)/12)

Alternatively, use the following normally-entered formula (press Enter as
usual):

=SUMPRODUCT(INT(A1:A100)+MOD(A1:A100,1)/12)/COUNT(A1:A100)

Of course, COUNT(A1:A100) could be replaced with simply 100.

 

[Cory Boyd]

I may be missing something fundamental here, but that looks like th
right answer to me. What are you expecting it to be?

Just to test it, put this formula in cell E2 and copy down
=AVERAGE(D$22)
It will allow you to see the running average and by the time you get t
cell
E7 you will have your 22'.56" average.

it was the f.ii notation gumming things up. the # ??/12 custom forma
was a good fix.

Thank you both for your help