Average IF? A1:A1000 has numbers - B1:B1000 has Active or Inactive
 

Average IF? A1:A1000 has numbers - B1:B1000 has Active or Inactive.
How do I run an average of column A's numbers that correspond to
column B's active?

25 Active
34 Active
60 Inactive
20 Active
65 Inactive
90 Active

Average IF? A1:A1000 has numbers - B1:B1000 has Active or Inactive
 

Haven't you asked this already? And been given several (very similar)
answers?

One way:

Array Enter (CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF(B1:B1000="Active",A1:A1000))



In article m,
wx4usa wrote:

Average IF? A1:A1000 has numbers - B1:B1000 has Active or Inactive.
How do I run an average of column A's numbers that correspond to
column B's active?

25 Active
34 Active
60 Inactive
20 Active
65 Inactive
90 Active

Average IF? A1:A1000 has numbers - B1:B1000 has Active or Inactive
 

Try one of these. Assuming there are no empty cells within the range:

=SUMIF(B1:B1000,"Active",A1:A1000)/COUNTIF(B1:B1000,"Active")

Or this array formula** :

=AVERAGE(IF(B1:B1000="Active",A1:A1000))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"wx4usa" wrote in message
s.com...
Average IF? A1:A1000 has numbers - B1:B1000 has Active or Inactive.
How do I run an average of column A's numbers that correspond to
column B's active?

25 Active
34 Active
60 Inactive
20 Active
65 Inactive
90 Active

Average IF? A1:A1000 has numbers - B1:B1000 has Active or Inactive
 

There's no AVERAGEIF function, but there are SUMIF and COUNTIF functions. So,
assuming your numbers are in A1:A6, and the statuses are in B1:B6, the
formula would be:

=SUMIF(B1:B6,"Active",A1:A6)/COUNTIF(B1:B6,"Active")

--
* Please click Yes if this was helpful *
Andy Smith
Senior Systems Analyst
Standard and Poor''s, NYC



"wx4usa" wrote:

Average IF? A1:A1000 has numbers - B1:B1000 has Active or Inactive.
How do I run an average of column A's numbers that correspond to
column B's active?

25 Active
34 Active
60 Inactive
20 Active
65 Inactive
90 Active

Average IF? A1:A1000 has numbers - B1:B1000 has Active or Inactive
 

There is an AVERAGEIF function, it was introduced in Excel 2007 and of
course one can use

=AVERAGE(IF(B1:B6="Active",A1:A6)) entered with ctrl + shift & enter

but the OP doesn't know how to use the CDO (webinterface of the newsgroups)
since he asked basically the same question yesterday and got 3 answers then



--


Regards,


Peo Sjoblom



"Andy Smith" wrote in message
...
There's no AVERAGEIF function, but there are SUMIF and COUNTIF functions.
So,
assuming your numbers are in A1:A6, and the statuses are in B1:B6, the
formula would be:

=SUMIF(B1:B6,"Active",A1:A6)/COUNTIF(B1:B6,"Active")

--
* Please click Yes if this was helpful *
Andy Smith
Senior Systems Analyst
Standard and Poor''s, NYC



"wx4usa" wrote:

Average IF? A1:A1000 has numbers - B1:B1000 has Active or Inactive.
How do I run an average of column A's numbers that correspond to
column B's active?

25 Active
34 Active
60 Inactive
20 Active
65 Inactive
90 Active

Average IF? A1:A1000 has numbers - B1:B1000 has Active or Inactive
 

On Oct 23, 12:35 pm, "Peo Sjoblom" wrote:
There is an AVERAGEIF function, it was introduced in Excel 2007 and of
course one can use

=AVERAGE(IF(B1:B6="Active",A1:A6)) entered with ctrl + shift & enter

but the OP doesn't know how to use the CDO (webinterface of the newsgroups)
since he asked basically the same question yesterday and got 3 answers then

--

Regards,

Peo Sjoblom

"Andy Smith" wrote in message

...

There's no AVERAGEIF function, but there are SUMIF and COUNTIF functions.
So,
assuming your numbers are in A1:A6, and the statuses are in B1:B6, the
formula would be:

=SUMIF(B1:B6,"Active",A1:A6)/COUNTIF(B1:B6,"Active")

--
* Please click Yes if this was helpful *
Andy Smith
Senior Systems Analyst
Standard and Poor''s, NYC

"wx4usa" wrote:

Average IF? A1:A1000 has numbers - B1:B1000 has Active or Inactive.
How do I run an average of column A's numbers that correspond to
column B's active?

25 Active
34 Active
60 Inactive
20 Active
65 Inactive
90 Active

Actually my post yesterday was different. It was referring to MAX IF.
Sorry all.

Average IF? A1:A1000 has numbers - B1:B1000 has Active or Inactive
 

Well if the solution to conditional MAX was given yesterday one might have
thought that you at least could have tried the same technique and just
replace MAX with AVERAGE, no?



--


Regards,


Peo Sjoblom


"wx4usa" wrote in message
ups.com...
On Oct 23, 12:35 pm, "Peo Sjoblom" wrote:
There is an AVERAGEIF function, it was introduced in Excel 2007 and of
course one can use

=AVERAGE(IF(B1:B6="Active",A1:A6)) entered with ctrl + shift & enter

but the OP doesn't know how to use the CDO (webinterface of the
newsgroups)
since he asked basically the same question yesterday and got 3 answers
then

--

Regards,

Peo Sjoblom

"Andy Smith" wrote in message

...

There's no AVERAGEIF function, but there are SUMIF and COUNTIF
functions.
So,
assuming your numbers are in A1:A6, and the statuses are in B1:B6, the
formula would be:

=SUMIF(B1:B6,"Active",A1:A6)/COUNTIF(B1:B6,"Active")

--
* Please click Yes if this was helpful *
Andy Smith
Senior Systems Analyst
Standard and Poor''s, NYC

"wx4usa" wrote:

Average IF? A1:A1000 has numbers - B1:B1000 has Active or Inactive.
How do I run an average of column A's numbers that correspond to
column B's active?

25 Active
34 Active
60 Inactive
20 Active
65 Inactive
90 Active

Actually my post yesterday was different. It was referring to MAX IF.
Sorry all.

Average IF? A1:A1000 has numbers - B1:B1000 has Active or Inactive
 

On Oct 23, 2:21 pm, "Peo Sjoblom" wrote:
Well if the solution to conditional MAX was given yesterday one might have
thought that you at least could have tried the same technique and just
replace MAX with AVERAGE, no?

--

Regards,

Peo Sjoblom

"wx4usa" wrote in message

ups.com...

On Oct 23, 12:35 pm, "Peo Sjoblom" wrote:
There is an AVERAGEIF function, it was introduced in Excel 2007 and of
course one can use

=AVERAGE(IF(B1:B6="Active",A1:A6)) entered with ctrl + shift & enter

but the OP doesn't know how to use the CDO (webinterface of the
newsgroups)
since he asked basically the same question yesterday and got 3 answers
then

--

Regards,

Peo Sjoblom

"Andy Smith" wrote in message

...

There's no AVERAGEIF function, but there are SUMIF and COUNTIF
functions.
So,
assuming your numbers are in A1:A6, and the statuses are in B1:B6, the
formula would be:

=SUMIF(B1:B6,"Active",A1:A6)/COUNTIF(B1:B6,"Active")

--
* Please click Yes if this was helpful *
Andy Smith
Senior Systems Analyst
Standard and Poor''s, NYC

"wx4usa" wrote:

Average IF? A1:A1000 has numbers - B1:B1000 has Active or Inactive.
How do I run an average of column A's numbers that correspond to
column B's active?

25 Active
34 Active
60 Inactive
20 Active
65 Inactive
90 Active

Actually my post yesterday was different. It was referring to MAX IF.
Sorry all.

Peo, Yes, I understand now. I guess thats why am am posting/asking
questions and not answering them. Im not the sharpest tool in the shed
as they say down home. Im not an expert for sure.
Thank you so much for your patience and help. I appreciate it very
much! Hal

Average IF? A1:A1000 has numbers - B1:B1000 has Active or Inactive
 

Peo, Yes, I understand now. I guess thats why am am posting/asking
questions and not answering them. Im not the sharpest tool in the shed
as they say down home. Im not an expert for sure.
Thank you so much for your patience and help. I appreciate it very
much! Hal


No problems Hal, you are a very courteous man and I apologize if I sounded a
bit harsh.
It just that many of us regulars feel like it is wasted time, not
particularly in your case but sometimes
when you do something that is rather complicated and maybe even spend 30-60
minutes (or even more) helping out with a solution only to notice later that
the same person posted the same question in another newsgroup 2 hours
earlier and that he/she got an answer already.
One tip I can give, it is always better if you acknowledge if the suggested
solution did work by posting to the same thread. While I realize that you
use the Google web interface most of the regulars use a dedicated newsreader
and it is easier for us if we see that the solution worked and if it didn't
what didn't work (wrong result etc)

Anyway, glad it worked for you


All the Best

Peo