applying function depending on condition in other cell

Hi experts,

If I have data like this

A B
-------------
x 2
x 5
y 6
x 1
y 2
x 3

How do I include the values of column B in a function, only if the
values have a corresponding x in the A column. For example if I would
like to calculate the AVERAGE only of the values in B which has the
value X in A.

Jonas

Try:

Assumes data is in range a1 to b10

=Sumif(a1:a10,"x",b1:b10)/countif(a1:a10,"x")

HTH

"jonnegoogle@yahoo.com" wrote:

> Hi experts,
>
> If I have data like this
>
> A B
> -------------
> x 2
> x 5
> y 6
> x 1
> y 2
> x 3
>
> How do I include the values of column B in a function, only if the
> values have a corresponding x in the A column. For example if I would
> like to calculate the AVERAGE only of the values in B which has the
> value X in A.
>
> Jonas
>
>

Great! Thanks a lot, I've always thought there was a smarter way to do
this than using only the "if"-statement and several new columns of
space, but I never new about "sumif" and "countif".
This is of course very appropriate for simple things as summing or
averaging, but is there also a more general way to do similar things
for an arbitrary function?

Jonas, very gratetful for the quick answer!

My solution shows you that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


<jonnegoogle@yahoo.com> wrote in message
news:1114084140.684205.32870@z14g2000cwz.googlegroups.com...
> Great! Thanks a lot, I've always thought there was a smarter way to do
> this than using only the "if"-statement and several new columns of
> space, but I never new about "sumif" and "countif".
> This is of course very appropriate for simple things as summing or
> averaging, but is there also a more general way to do similar things
> for an arbitrary function?
>
> Jonas, very gratetful for the quick answer!
>

Thanks!
Today is a very nice day, never heard about array formulas before.
Seems extremely useful.

Jonas

or

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

which is an array formula, so is committed with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Toppers" <Toppers@discussions.microsoft.com> wrote in message
news:2A783AB9-31A7-4354-9C5C-400BE0E51774@microsoft.com...
> Try:
>
> Assumes data is in range a1 to b10
>
> =Sumif(a1:a10,"x",b1:b10)/countif(a1:a10,"x")
>
> HTH
>
> "jonnegoogle@yahoo.com" wrote:
>
> > Hi experts,
> >
> > If I have data like this
> >
> > A B
> > -------------
> > x 2
> > x 5
> > y 6
> > x 1
> > y 2
> > x 3
> >
> > How do I include the values of column B in a function, only if the
> > values have a corresponding x in the A column. For example if I would
> > like to calculate the AVERAGE only of the values in B which has the
> > value X in A.
> >
> > Jonas
> >
> >