average if question

[ptrifile]

I am working in excel and need to know if it is possible to do the following:

lets say my data looks like the following

name time
abc 0:1:11
abc 0:1:15
ddd 0:2:30
abc 0:1:45
abc 0:2:00
ddd 0:1:30
eee 0:2:00
abc 0:1:30
eee 0:2:30

is there a way to do an average if to average only the times that have "abc" next to them? there is a pretty large spreadsheet with different names all of the spreadsheet so a simple sort wont work....can someone help?

Thanks in advance

Paul

 

[SkipVought]

Hi,

Just a word regarding the semantic difference between "time" and "duration." Both Date and Time are considered POINTS in the time continum. Just as you would never ADD or SUBTRACT 12/25/2010 and 12/25/2011, you would never do so to 2 Time values.

I assume that you have DURATIONS of hours, minutes and seconds, stored as days, and if you were to FORMAT that column as GENERAL, you would see DECIMAL values.

If you have Excel 2007 or greater, you have AVERAGEIF funcions. Follow the bouncing ball to use.

If you have Excel 2003 or earlier, and using Named Ranges, in accordance with headings Name & Dur (rather than TIME)...

=SUMPRODUCT((Name="abc")*(Dur))/Countif(Name,"abc")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}