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
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 |
#3
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 |
#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 |
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) |