Average if

**WildWill** · 10-10-2008, 10:26 AM

Hi

I would like to understand what the formula would look like to achieve the following:

Column A Column B
P1 08:22
P2 02:45
P1 03:15
P3 00:45
P3 11:14
P2 21:07

Column A is formated to contain Text
Column B is formated to contain HH:MM data
I now want to calculate the average time (HH:MM) for all the occurences of "P1" in Column A. (i.e. what is the average of 08:22 and 03:15)

Please help.

**Paul** · 10-10-2008, 10:33 AM

Hi WildWill, and welcome to the forum.

You can average time values in Excel, because Excel stores time and date values as numbers. In your example, you should be able to use the array formula:

=AVERAGE(IF(A1:A6="P1",B1:B6))

After typing or editing this formula, you must press CTRL+SHIFT+ENTER, not just ENTER. This will insert braces, {}, around the formula automatically when done correctly.

Remember to format this cell as Time (hh:mm:ss), otherwise you'll see a decimal number, which represents a time of day.

**WildWill** · 10-10-2008, 11:05 AM

Excellent! What is the purpose of the CTRL+SHIFT+ENTER, not just ENTER. This will insert braces, {}?

**Paul** · 10-10-2008, 11:10 AM

Array formulas - formulas meant to work over a range of cells - need to be confirmed with CTRL+SHIFT+ENTER. Please visit the following links for more detailed explanations and examples:

http://www.cpearson.com/excel/ArrayFormulas.aspx

http://office.microsoft.com/en-us/ex...872901033.aspx

**daddylonglegs** · 10-10-2008, 11:22 AM

Hello Will,

Are your times clock times or elapsed times, i.e. the time something happened or the length of time something took?

**WildWill** · 10-10-2008, 01:13 PM

Originally Posted by WildWill

Hi

I would like to understand what the formula would look like to achieve the following:

Column A Column B
P1 08:22
P2 02:45
P1 03:15
P3 00:45
P3 11:14
P2 21:07

Column A is formated to contain Text
Column B is formated to contain HH:MM data
I now want to calculate the average time (HH:MM) for all the occurences of "P1" in Column A. (i.e. what is the average of 08:22 and 03:15)

Please help.

Sorted on the above query! Now, considering the above data set, I want to count the number of cells in Column B which have values in them (like a COUNTA) but only where Column A = "P3", for example. I.e. the answer will be "2" for this example. Awesome Forum!