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.

Hi Cory,

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

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

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

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

in cell D12, I have the formula =AVERAGE(D2:D10) and it returns the result 22'.56", which I can't wrap my head around. I have tried adjusting the formula cell range to D2:D7 and it still returns the same result.

Thanks.

"Cory Boyd" wrote:
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.

I may be missing something fundamental here, but that looks like the 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$2:D2)
It will allow you to see the running average and by the time you get to cell
E7 you will have your 22'.56" average.

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

Thank you both for your help!

On Tuesday, February 5, 2013 at 5:29:00 PM UTC-6, Cory Boyd wrote:
Spencer101;1609135 Wrote:
I may be missing something fundamental here, but that looks like the
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$2:D2)
It will allow you to see the running average and by the time you get to
cell
E7 you will have your 22'.56" average.

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

Thank you both for your help!




--
Cory Boyd


The # ??/12 is a good way to enter in the numbers, but does anyone have the equation then for figuring the averages of those numbers. Mine is making me enter them as decimals (12.25 then automatically converts to 12 3/12. It still works, just need to know what decimal equals each inch out of 12). Is there any way to enter them in this format 12 3/12 and have them automatically calculate the avg. in the same format?

The # ??/12 is a good way to enter in the numbers, but does anyone
have the equation then for figuring the averages of those numbers.
Mine is making me enter them as decimals (12.25 then automatically
converts to 12 3/12. It still works, just need to know what decimal
equals each inch out of 12). Is there any way to enter them in this
format 12 3/12 and have them automatically calculate the avg. in the
same format?

Strange format! 12 3/12 is actually 12 1/4 (decimal value 12.25
converted to fraction)...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion