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

  • Thread starter Thread starter wx4usa
  • Start date Start date
W

wx4usa

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
 
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))
 
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)
 
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")
 
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
 
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
Click to expand...

Actually my post yesterday was different. It was referring to MAX IF.
Sorry all.
 
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
 
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
Click to expand...

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
 
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
Click to expand...


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
 
Back
Top