Home 
Search 
Today's Posts 
#1




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




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




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 pasteSpecial 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 (nonarray) 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




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




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 (nonarray) 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 cutandpasting 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




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




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 **arrayentered** 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 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
numbers & decimals  New Users to Excel  
Sorting numbers with two decimals  Excel Worksheet Functions  
How do I align decimals in Excel when not all numbers have % sym.  Excel Discussion (Misc queries)  
Summing large numbers with decimals in Excel  Excel Discussion (Misc queries)  
Excel: How can I get numbers without decimals ?  New Users to Excel 