Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change color for active row numbers and column letters | Excel Discussion (Misc queries) | |||
average of kth largest numbers in an array of n numbers | Excel Worksheet Functions | |||
Average of numbers in column between to other numbers | Excel Discussion (Misc queries) | |||
update row numbers after different active cells in macros followi. | Excel Discussion (Misc queries) | |||
HOW TO COPY 480 ACTIVE E-MAIL ADDRESSES CLM "G" ON AN ACTIVE EXCE. | Excel Discussion (Misc queries) |