how do I get excel to add,then average numbers with two decimals?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wenz324 - 02 Feb 2008 10:27 GMT

ie 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

Reply to this Message

Charles Williams - 02 Feb 2008 11:08 GMT

If they all start with 3 minutes then you can convert them to times by Edit
Replace 3. with 3:
Then format them with a custom format hh:mm:ss.00 so you can see whats
happening

Then use this array formula (assumes best means smallest time)

=AVERAGE(SMALL($A$1:$A$4,{1,2,3}))

you need to enter this array formula using Control-Shift-Enter

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

> ie I need to tally little athletics results. I need the average of the
> best
[quoted text clipped - 10 lines]
> 3.23.94
> 3.56.29

Reply to this Message

T. Valko - 02 Feb 2008 18:23 GMT

> =AVERAGE(SMALL($A$1:$A$4,{1,2,3}))
>you need to enter this array formula using Control-Shift-Enter

No need to array enter.

Signature

Biff
Microsoft Excel MVP

> If they all start with 3 minutes then you can convert them to times by
> Edit Replace 3. with 3:
[quoted text clipped - 26 lines]
>> 3.23.94
>> 3.56.29

Reply to this Message

joeu2004@hotmail.com - 02 Feb 2008 11:23 GMT

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

> I need to tally little athletics results. I need the average of the best
> three scores.
[quoted text clipped - 7 lines]
> 3.23.94
> 3.56.29

Reply to this Message

joeu2004@hotmail.com - 02 Feb 2008 11:28 GMT

Errata....

I wrote:

> 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

I guess the best of 3 is SMALL(), not LARGE().

Also, "data" should be A1:A5. An artifact of cut-and-pasting from my
worksheet. Note that "ROW(A1:A3)" is simply a way of getting the numbers 1
through 3. It has nothing to do with the data in column A.

Reply to this Message

Gary''s Student - 02 Feb 2008 11:24 GMT

With data in A1, In B1 enter:
=TIME(0,LEFT(A1,1),MID(A1,3,2))+RIGHT(A1,2)/(24*600*600)
and format as Custom m:ss.00

Signature

Gary''s Student - gsnu200767

> ie I need to tally little athletics results. I need the average of the best
> three scores.
[quoted text clipped - 8 lines]
> 3.23.94
> 3.56.29

Reply to this Message

Ron Rosenfeld - 02 Feb 2008 19:38 GMT

>ie I need to tally little athletics results. I need the average of the best
>three scores.
[quoted text clipped - 8 lines]
>3.23.94
>3.56.29

If you don't want to convert your data, you could use these **array-entered**
formulas:

Add all the results:

=SUM(--("0:"&SUBSTITUTE(A1:A5,".",":",1)))

Average the best three (I have assumed that "best" means "lowest")

=AVERAGE(SMALL(--("0:"&SUBSTITUTE(A1:A5,".",":",1)),{1,2,3}))

Format your results as something like [m]:ss.00
--ron

Reply to this Message