I have two columns of data, one of which I need to calculate an average of
but only if the data in the 2nd column is of a certain value.
I can't work out how to do this...
For instance, I need to calculate the average number of calls taken within
week 37.
My columns look similar to:
No. of calls Week no.
10 37
8 38
4 37 etc

This is an array formula that must be entered using ctrl+shift+enter at the
same time.

=AVERAGE(IF(M1:M3=37,L1:L3))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Rebekah" <(E-Mail Removed)> wrote in message
news:0C353E9A-21C4-4FA6-96E6-(E-Mail Removed)...
>I have two columns of data, one of which I need to calculate an average of
> but only if the data in the 2nd column is of a certain value.
> I can't work out how to do this...
> For instance, I need to calculate the average number of calls taken within
> week 37.
> My columns look similar to:
> No. of calls Week no.
> 10 37
> 8 38
> 4 37 etc

This is returning #DIV/0!

Is this because the "No. of calls" column is also subject to a formula?



"Don Guillett" wrote:

> This is an array formula that must be entered using ctrl+shift+enter at the
> same time.
>
> =AVERAGE(IF(M1:M3=37,L1:L3))
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Rebekah" <(E-Mail Removed)> wrote in message
> news:0C353E9A-21C4-4FA6-96E6-(E-Mail Removed)...
> >I have two columns of data, one of which I need to calculate an average of
> > but only if the data in the 2nd column is of a certain value.
> > I can't work out how to do this...
> > For instance, I need to calculate the average number of calls taken within
> > week 37.
> > My columns look similar to:
> > No. of calls Week no.
> > 10 37
> > 8 38
> > 4 37 etc
>
>

Worked on the data provided. Send me a workbook to the address below along
with these posts

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Rebekah" <(E-Mail Removed)> wrote in message
news:1C699235-A93E-45F0-9DF2-(E-Mail Removed)...
> This is returning #DIV/0!
>
> Is this because the "No. of calls" column is also subject to a formula?
>
>
>
> "Don Guillett" wrote:
>
>> This is an array formula that must be entered using ctrl+shift+enter at
>> the
>> same time.
>>
>> =AVERAGE(IF(M1:M3=37,L1:L3))
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Rebekah" <(E-Mail Removed)> wrote in message
>> news:0C353E9A-21C4-4FA6-96E6-(E-Mail Removed)...
>> >I have two columns of data, one of which I need to calculate an average
>> >of
>> > but only if the data in the 2nd column is of a certain value.
>> > I can't work out how to do this...
>> > For instance, I need to calculate the average number of calls taken
>> > within
>> > week 37.
>> > My columns look similar to:
>> > No. of calls Week no.
>> > 10 37
>> > 8 38
>> > 4 37 etc
>>
>>