View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld Ron Rosenfeld is offline
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