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

" 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!

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" wrote in message
...
Try:

Assumes data is in range a1 to b10

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

HTH

" 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

My solution shows you that.

--

HTH

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


wrote in message
ups.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