Conditional Average

[MEK911]

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

[swatsp0p]

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(G22:J22),"NOT FOUND")

Is this what you are looking for?

[MEK911]

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...



thanks!

-mek

[David Billigmeier]

=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
>
>

[Myrna Larson]

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
<[email protected]> 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