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

On Fri, 9 Sep 2005 10:27:28 -0500, MEK911
<MEK911.1v3n2k_1126281951.9242@excelforum-nospam.com> 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

I'm interpreting this to mean that you have a column containing the
value (in this example the word "Yes") you're interested in, in say
A3:A100, and the numbers you want to average in B3:B100

So in B1 put

=SUMIF(A3:A100,"Yes",B3:B100)/COUNTIF(A3:A100,"Yes")

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

One way ..

Assume you have numbers in A1:A10,
and certain cells marked as "X" within B1:B10

We could put in say, C1, and array-enter the formula
(i.e press CTRL+SHIFT+ENTER):

=AVERAGE(IF(B1:B10="X",A1:A10))

which will evaluate the average of only those numbers in col A
corresponding to the "x" marked in col B
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"MEK911" <MEK911.1v3n2k_1126281951.9242@excelforum-nospam.com> wrote in
message news:MEK911.1v3n2k_1126281951.9242@excelforum-nospam.com...
>
> 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=466284
>

Without knowing your exact cell references I'll just use row 1 for the check
and row 2 as the corresponding numbers to average, in that case:

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

Array formula so enter with Ctrl+Shift+Enter


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