A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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



 
 
Thread Tools Display Modes
  #1  
Old February 2nd 08, 10:27 AM posted to microsoft.public.excel.worksheet.functions
wenz324
external usenet poster
 
Posts: 1
Default how do I get excel to add,then average numbers with two decimals?

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

Ads
  #2  
Old February 2nd 08, 11:08 AM posted to microsoft.public.excel.worksheet.functions
Charles Williams
external usenet poster
 
Posts: 968
Default how do I get excel to add,then average numbers with two decimals?

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

"wenz324" > wrote in message
...
> 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
>



  #3  
Old February 2nd 08, 11:23 AM posted to microsoft.public.excel.worksheet.functions
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


  #4  
Old February 2nd 08, 11:24 AM posted to microsoft.public.excel.worksheet.functions
Gary''s Student
external usenet poster
 
Posts: 11,059
Default how do I get excel to add,then average numbers with two decimals?

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

--
Gary''s Student - gsnu200767


"wenz324" wrote:

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

  #5  
Old February 2nd 08, 11:28 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 418
Default how do I get excel to add,then average numbers with two decima

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.
  #6  
Old February 2nd 08, 06:23 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,768
Default how do I get excel to add,then average numbers with two decimals?

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


--
Biff
Microsoft Excel MVP


"Charles Williams" > wrote in message
...
> 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
>
> "wenz324" > wrote in message
> ...
>> 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
>>

>
>



  #7  
Old February 2nd 08, 07:38 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 5,651
Default how do I get excel to add,then average numbers with two decimals?

On Sat, 2 Feb 2008 02:27:01 -0800, wenz324 >
wrote:

>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


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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
numbers & decimals chas. New Users to Excel 1 October 15th 07 05:36 AM
Sorting numbers with two decimals Leslie W. Excel Worksheet Functions 4 June 1st 07 08:54 PM
How do I align decimals in Excel when not all numbers have % sym. sjgiles Excel Discussion (Misc queries) 2 September 7th 06 10:36 PM
Summing large numbers with decimals in Excel PradeepKel Excel Discussion (Misc queries) 1 June 6th 06 04:12 AM
Excel: How can I get numbers without decimals ? Wamalapada New Users to Excel 2 April 9th 05 01:43 PM


All times are GMT +1. The time now is 11:20 PM.


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