Look for data in one column and then return mean average calculated from another column

**hpofql** · 05-30-2012, 05:56 AM

Am trying to find a non-complicated way of getting the following. I have data more or less as follows (on a much bigger scale)

Column A Column B Column C
Petra Red 3.5
John Blue 4.2
Alice Green 3.8
Simon Blue 2.7

I would like Excel to be able to look in Column B for "Blue", then look at the results in Column C for those 'Blues' and return the mean average of their results (in this example, the average of 4.2 + 2.7). Is there a way I can do this, preferably without having to resort to pivot tables?

Many thanks!

**Fotis1991** · 05-30-2012, 06:01 AM

Hi

Try this array formula(C+S+E)

=AVERAGE(IF(B1:B4="blue",C1:C4))

Is this works, for you?

**TMS** · 05-30-2012, 06:02 AM

One way:

=SUMIF(B:B,"blue",C:C)/COUNTIF(B:B,"blue")

or

=SUMIF(B:B,$D2,C:C)/COUNTIF(B:B,$D$2) if the cell D2 has the value "blue"

Regards, TMS

**tom1977** · 05-30-2012, 06:03 AM

Hi try this
=AVERAGEIFS(C1:C4,B1:B4,"Blue")

**hpofql** · 05-30-2012, 07:09 AM

Thank you so much TMS and tom1977! Both did the trick. Never heard of AVERAGEIFS before. Very useful to know! Fotis, your response worked for 'Blue' but couldn't get it to work for the other colours but thank you for trying.

**Fotis1991** · 05-30-2012, 07:25 AM

Both others solutions are betters, by meaning are not Array Formulae.

But this is something and it's something different to say that my formula works, only for a color!

How this can be done? Does not like the others colors??

Look for data in one column and then return mean average calculated from another column

LinkBack

Thread Tools

Rate This Thread

Display

Look for data in one column and then return mean average calculated from another column

Re: Look for data in one column and then return mean average calculated from another colum

Re: Look for data in one column and then return mean average calculated from another colum

Re: Look for data in one column and then return mean average calculated from another colum

Re: Look for data in one column and then return mean average calculated from another colum

Re: Look for data in one column and then return mean average calculated from another colum

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions