How to average where 1 cell = x

[Ian]

Howdy,
Hopefully someone can help me out with this problem.

I have a list of data, and I need to find averages for each month. Is there
any function I can use that would effectively calculate: where [column A] =
x, then average column B values that correspond to x in column A?

Thanks in advance,

Ian

 

[Guest]

Depending on how your worksheet is set up, you may wantto try Sorting by your
Month Column and then using the Subtotals feature located in the Data menu.

tj

 

[Don Guillett]

This is an array formula so must be entered/edited with ctrl+shift+enter
=AVERAGE(IF(C9:C11="x",D911))

 

[Guest]

Alternatively, this Formula may do what you want:
{=SUM(IF(A1:A11="x",B1:B11,0))}

This Formula assumes the values you want to equal "x" are in A1:A11, and the
Values you want to add together are in B1:B11.
This is an Array Formula. That means you must press Ctrl+Shift+Enter.
You do not type the curly brackets--they are the result of Ctrl+Shift+Enter.

tj

 

[Guest]

Don is right, I typed Sum whrn I meant Average.

tj

This is an array formula so must be entered/edited with ctrl+shift+enter
=AVERAGE(IF(C9:C11="x",D911))

--
Don Guillett
SalesAid Software
(e-mail address removed)

Howdy,
Hopefully someone can help me out with this problem.

I have a list of data, and I need to find averages for each month. Is there
any function I can use that would effectively calculate: where [column A] =
x, then average column B values that correspond to x in column A?

Thanks in advance,

Ian

 

[Ian]

Bloody hell, that's genius! Thanks guys - both those features work! I
thought I knew more than the average Joe about Excel - but arrays are
something I'll have to read more into. Didn't know anything about them in
Excel, and they seem very useful.

Thanks again,

Ian

This is an array formula so must be entered/edited with ctrl+shift+enter
=AVERAGE(IF(C9:C11="x",D911))

--
Don Guillett
SalesAid Software
(e-mail address removed)

Howdy,
Hopefully someone can help me out with this problem.

I have a list of data, and I need to find averages for each month. Is there
any function I can use that would effectively calculate: where [column

A]

=
x, then average column B values that correspond to x in column A?

Thanks in advance,

Ian

 

[Ian]

Could you work through it saying what the various sections of the equation
do?

How would I go about applying the average to column B values over several
column A values at once (i.e. 1,2, and 3, instead of just 1)?

Thanks again,

Ian

Depending on how your worksheet is set up, you may wantto try Sorting by your
Month Column and then using the Subtotals feature located in the Data menu.

tj

Howdy,
Hopefully someone can help me out with this problem.

I have a list of data, and I need to find averages for each month. Is there
any function I can use that would effectively calculate: where [column A] =
x, then average column B values that correspond to x in column A?

Thanks in advance,

Ian

 

[Aladin Akyurek]

...
How would I go about applying the average to column B values ove
several
column A values at once (i.e. 1,2, and 3, instead of just 1)?
...

=AVERAGE(IF(ISNUMBER(MATCH(A2:A100,{1,2,3},0)),B2:B100)))

which uou need to confirm with control+shift+enter instead of just wit
enter.

For "array formulas" consult the Help file and do a search usin
Google

 

[Don Guillett]

glad to help

--
Don Guillett
SalesAid Software
(e-mail address removed)

Bloody hell, that's genius! Thanks guys - both those features work! I
thought I knew more than the average Joe about Excel - but arrays are
something I'll have to read more into. Didn't know anything about them in
Excel, and they seem very useful.

Thanks again,

Ian

This is an array formula so must be entered/edited with ctrl+shift+enter
=AVERAGE(IF(C9:C11="x",D911))

[column

A]

=
x, then average column B values that correspond to x in column A?

Thanks in advance,

Ian