Averaging COUNTIF
 

I want to average numbers in column B, only in column A has a specific term
(in other words, an average of all rates in column B where column A contains
the word "DG").

I am figure I need to use COUNTIF, OFFSET, and AVERAGE functions, but I am
having trouble putting them together. Thanks!

Hi Wynn

only SUMIF and COUNTIF :)

=SUMIF(A1:A10,"DG",B1:B10)/COUNTIF(A1:A10,"DG")

Cheers
JulieD

"Wynn" wrote in message
...
I want to average numbers in column B, only in column A has a specific term
(in other words, an average of all rates in column B where column A
contains
the word "DG").

I am figure I need to use COUNTIF, OFFSET, and AVERAGE functions, but I am
having trouble putting them together. Thanks!

Hi
try the following array formula
=AVERAGE(IF(A1:A100="DG",B1:B100))

--
Regards
Frank Kabel
Frankfurt, Germany

"Wynn" schrieb im Newsbeitrag
...
I want to average numbers in column B, only in column A has a
specific term
(in other words, an average of all rates in column B where column A
contains
the word "DG").

I am figure I need to use COUNTIF, OFFSET, and AVERAGE functions, but
I am
having trouble putting them together. Thanks!

Frank,
Thank you. This is working very well. I am having a problem however,
because I want to test values for two columns and it does not seem to be
working with the AND function. Am I missing something?:

array formula
=AVERAGE(IF(AND(A1:A100="DG",C1:C100="BG"),B1:B100 ))

"Frank Kabel" wrote:

Hi
try the following array formula
=AVERAGE(IF(A1:A100="DG",B1:B100))

--
Regards
Frank Kabel
Frankfurt, Germany

"Wynn" schrieb im Newsbeitrag
...
I want to average numbers in column B, only in column A has a
specific term
(in other words, an average of all rates in column B where column A
contains
the word "DG").

I am figure I need to use COUNTIF, OFFSET, and AVERAGE functions, but
I am
having trouble putting them together. Thanks!

Hi
for some general stuff about this see:
http://www.dicks-blog.com/archives/2...rations-in-arr
ay-formulas/trackback/

In your case try:
=AVERAGE(IF((A1:A100="DG")*(C1:C100="BG"),B1:B100) )

--
Regards
Frank Kabel
Frankfurt, Germany

"Wynn" schrieb im Newsbeitrag
...
Frank,
Thank you. This is working very well. I am having a problem
however,
because I want to test values for two columns and it does not seem to
be
working with the AND function. Am I missing something?:

array formula
=AVERAGE(IF(AND(A1:A100="DG",C1:C100="BG"),B1:B100 ))

"Frank Kabel" wrote:

Hi
try the following array formula
=AVERAGE(IF(A1:A100="DG",B1:B100))

--
Regards
Frank Kabel
Frankfurt, Germany

"Wynn" schrieb im Newsbeitrag
...
I want to average numbers in column B, only in column A has a
specific term
(in other words, an average of all rates in column B where column
A
contains
the word "DG").

I am figure I need to use COUNTIF, OFFSET, and AVERAGE functions,
but
I am
having trouble putting them together. Thanks!

Thanks, Frank - you always come through for me. Thanks for the site as well.

"Frank Kabel" wrote:

Hi
for some general stuff about this see:
http://www.dicks-blog.com/archives/2...rations-in-arr
ay-formulas/trackback/

In your case try:
=AVERAGE(IF((A1:A100="DG")*(C1:C100="BG"),B1:B100) )

--
Regards
Frank Kabel
Frankfurt, Germany

"Wynn" schrieb im Newsbeitrag
...
Frank,
Thank you. This is working very well. I am having a problem
however,
because I want to test values for two columns and it does not seem to
be
working with the AND function. Am I missing something?:

array formula
=AVERAGE(IF(AND(A1:A100="DG",C1:C100="BG"),B1:B100 ))

"Frank Kabel" wrote:

Hi
try the following array formula
=AVERAGE(IF(A1:A100="DG",B1:B100))

--
Regards
Frank Kabel
Frankfurt, Germany

"Wynn" schrieb im Newsbeitrag
...
I want to average numbers in column B, only in column A has a
specific term
(in other words, an average of all rates in column B where column
A
contains
the word "DG").

I am figure I need to use COUNTIF, OFFSET, and AVERAGE functions,
but
I am
having trouble putting them together. Thanks!