MrExcel Publishing
Your One Stop for Excel Tips & Solutions

An Average IF problem


Posted by Tanya on August 13, 2001 10:52 PM

I have run into a brick wall, and was wondering if someone could
just point me in the right direction. I have week numbers in one
column, dates in a column, and response time in another column.
What I need to do is this:
If the date column is blank, then I want the average of the
response time column cells that correspond to week 1.

My basic issue is that I have a log of all incoming and
outgoing requests, along with the response time for each
request. I need weekly counts of incoming, outgoing, and
the average response time. Any ideas on the direction that I
can take to do this?


Posted by Aladin Akyurek on August 14, 2001 2:20 AM

Tanya,

=SUMPRODUCT((A1:A5=1)*(ISBLANK(B1:B5)*(C1:C5)))/SUMPRODUCT((A1:A5=1)*(ISBLANK(B1:B5)))

will do what you want. I assumed the week numbers to be in A1:A5, dates in B1:B5, response times in C1:C5.

Aladin

Posted by Mark W. on August 14, 2001 8:31 AM

Tanya, using Aladin's column defintions...

A:C = 'Week Nbr', 'Date', 'Response Time'

...use the following array formula...

{=AVERAGE(IF(A1:A5+B1:B5=1,C1:C5))}

Note: Array formulas are entered using the
Control+Shift+Enter key combination. The
braces, {}, are not entered by you -- they're
supplied by Excel in recognition of a properly
entered array formula.