View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
[email protected] joeu2004@hotmail.com is offline
external usenet poster
 
Posts: 418
Default how do I get excel to add,then average numbers with two decimals?

wenz324" wrote:
I need the average of the best three scores.
These are times (minutes, seconds and hundredths of seconds) and I now
realise we probably should have been entering using colons throughout the
season. I'm trying to fix the problem without having to manually change the
results into times.


You might consider converting the "times" first. The "manual" process might
not be as complicated as you think. If the times below are in A1:A5, put the
following formula into B1 and copy down through B5:

=--SUBSTITUTE(A1, ".", ":", 1)

Select and copy B1:B5, then paste-Special Value back to A1:A5, and format
A1:A5 as Custom "mm:ss.00". You can now remove B1:B5, which might show
#VALUE errors now anyway.

Now, you can average them in the normal manner. For example, for the
average of the best of 3, enter the following as an array formula
(ctrl+shift+Enter):

=AVERAGE(LARGE(data,ROW(A1:A3)))

Alternatively, the following regular (non-array) formula does the same thing:

=SUMPRODUCT(LARGE(data,ROW(A1:A3))) / 3

You will need to format the result as Custom "mm:ss.00".

HTH.


----- original posting -----

"wenz324" wrote:
I need to tally little athletics results. I need the average of the best
three scores.
These are times (minutes, seconds and hundredths of seconds) and I now
realise we probably should have been entering using colons throughout the
season. I'm trying to fix the problem without having to manually change the
results into times.
3.06.51
3.11.43
3.22.56
3.23.94
3.56.29