Conditional Average
 

Hi Everybody,

I am trying to do a conditonal AVERAGE on an excel spreadsheet and am
stuck...What I would like to do is check a one row for a certain value,
and if true, AVERAGE the corresponding values on another coumn.

Is this possible? thanks.

-mek


--
MEK911
------------------------------------------------------------------------
MEK911's Profile: http://www.excelforum.com/member.php...o&userid=27115
View this thread: http://www.excelforum.com/showthread...hreadid=466285

Let's assume you have a value in cell E23 that you want to look for in
Range G22:L22. If found, average the range of G22:L22. Use this
formula:

=IF(NOT(ISNA(HLOOKUP(E23,G22:J22,1,0))),AVERAGE(G2 2:J22),"NOT FOUND")

Is this what you are looking for?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=466285

=AVERAGE(IF(1:1="value to check",2:2,""))


--
Regards,
Dave
<!--


"MEK911" wrote:


Hi Everybody,

I am trying to do a conditonal AVERAGE on an excel spreadsheet and am
stuck...What I would like to do is check a one row for a certain value,
and if true, AVERAGE the corresponding values on another coumn.

Is this possible? thanks.

-mek


--
MEK911
------------------------------------------------------------------------
MEK911's Profile: http://www.excelforum.com/member.php...o&userid=27115
View this thread: http://www.excelforum.com/showthread...hreadid=466285

I am assuming this would check for the whole range and average any
corresponding values in another range?

For example, out of my entire sales report, I would like to average the
PRICE(N7:N81) column only for records that have New York in the
STATE(G7:G81) column...

:confused:

thanks!

-mek


--
MEK911
------------------------------------------------------------------------
MEK911's Profile: http://www.excelforum.com/member.php...o&userid=27115
View this thread: http://www.excelforum.com/showthread...hreadid=466285

Here's one way:

=SUMIF(A1:A100,"Smith",B1:B100)/COUNTIF(A1:A100,"Smith")

Here's another using an array formula (entered with CTRL+SHIFT+ENTER):

=AVERAGE(IF(A1:A100="Smith",B1:B100))




On Fri, 9 Sep 2005 10:34:29 -0500, MEK911
wrote:


Hi Everybody,

I am trying to do a conditonal AVERAGE on an excel spreadsheet and am
stuck...What I would like to do is check a one row for a certain value,
and if true, AVERAGE the corresponding values on another coumn.

Is this possible? thanks.

-mek