How to average values based on a condition in another column

K

Ken99

I have a spreadsheet with a number from 1-20 in column A for each record
(row). I have another number in column P representing a number of days for
each record. I need a summary formula which does the following:

For all the rows in column A that are less than 11 (have values from 1-10),
average all of the matching rows in column P.
 
B

Bob Phillips

=AVERAGE(IF(A1:A100<11,P1:p100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
K

Ken99

Ken99 said:
I have a spreadsheet with a number from 1-20 in column A for each record
(row). I have another number in column P representing a number of days for
each record. I need a summary formula which does the following:

For all the rows in column A that are less than 11 (have values from 1-10),
average all of the matching rows in column P.
Click to expand...
Thanks Bob--this was right on and solved the problem immediately.

--Ken
 
Top