Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 3
Default Averaging feet and inches (not decimal feet)

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.
  #2   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by Cory Boyd View Post
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.
  #3   Report Post  
Junior Member
 
Posts: 3
Default

Quote:
Originally Posted by Spencer101 View Post
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.
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 829
Default Averaging feet and inches (not decimal feet)

"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.

  #5   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by Cory Boyd View Post
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.
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.


  #6   Report Post  
Junior Member
 
Posts: 3
Default

Quote:
Originally Posted by Spencer101 View Post
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!
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Averaging feet and inches (not decimal feet)

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?
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Averaging feet and inches (not decimal feet)


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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
change feet and inches to decimal inches Brian 1 Excel Worksheet Functions 4 May 2nd 09 04:02 AM
convert decimal inches into feet and inches Jason Stripling Excel Discussion (Misc queries) 2 April 23rd 09 02:45 PM
Converting from feet, inches and fractions to inches and decimal p Dee Setting up and Configuration of Excel 5 September 18th 07 04:18 PM
how do i convert from feet/inches to decimal? wbfisher Excel Programming 1 September 15th 06 08:38 PM
How do i convert feet and inches to a decimal value? go_chrisg Excel Worksheet Functions 4 May 22nd 05 04:33 PM


All times are GMT +1. The time now is 11:29 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"