Average of times where text may be in range

[Guest]

Hi all,

I'm trying to generate the average time for a number of finishers in a
sailing race. There may be certain boats that have text values in place of a
finish time for did not finish, did not start or did not compete.

Q1 - The spreadsheet I inherited used SUM() to give the sum of the finish
times, COUNTIF(value >0) of the count of the number of finishers, finally to
arrive at average = SUM / COUNTIF. This gives errors as SUM() does not give
the correct value when the text values are present - are there functions
available to give the sum of the race times for the finishers (this figure
also used for other stats)?

Q2 - Does the AVERAGE() function ignore text when calculating the average?

Cheers!
Mark (Skiffie)

 

[Bob Phillips]

Q1 - The spreadsheet I inherited used SUM() to give the sum of the finish
times, COUNTIF(value >0) of the count of the number of finishers, finally to
arrive at average = SUM / COUNTIF. This gives errors as SUM() does not give
the correct value when the text values are present - are there functions
available to give the sum of the race times for the finishers (this figure
also used for other stats)?

That shouldn't matter, SUM will ignore text.

Q2 - Does the AVERAGE() function ignore text when calculating the average?

Yes.

 

[Guest]

That shouldn't matter, SUM will ignore text.

Thanks Bob - made me re-check the formula again and I found my range was
wrong.

 

[Bob Phillips]

We've all been there <g>

Bob

Thanks Bob - made me re-check the formula again and I found my range was
wrong.