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.
