| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average with specific criteria
I need to average the cells on column b that correspond to the criteria in
column a. If cells in column a match "EDCP", average the cells in column b that correspond to those cells, not the whole column. In this case it would equal 00:30. I will appreciate your help. Order Procedure Drawn to Received EDCP 0:06 CBCsD 0:06 UA 0:16 EDCP 0:59 CBCsD 0:59 Troponin-I 0:59 EDCP 0:25 -- Clari |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average with specific criteria
Try one of these...
All versions of Excel, array entered**: =AVERAGE(IF(A1:A7="EDCP",B1:B7)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Format as [h]:mm Excel 2007 only, normally entered: =AVERAGEIF(A1:A7,"EDCP",B1:B7) Format as [h]:mm -- Biff Microsoft Excel MVP "Clari" wrote in message ... I need to average the cells on column b that correspond to the criteria in column a. If cells in column a match "EDCP", average the cells in column b that correspond to those cells, not the whole column. In this case it would equal 00:30. I will appreciate your help. Order Procedure Drawn to Received EDCP 0:06 CBCsD 0:06 UA 0:16 EDCP 0:59 CBCsD 0:59 Troponin-I 0:59 EDCP 0:25 -- Clari |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average with specific criteria
=SUMIF(A:A,"EDCP",B:B)/COUNTIF(A:A,"EDCP")
-- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Clari" wrote: I need to average the cells on column b that correspond to the criteria in column a. If cells in column a match "EDCP", average the cells in column b that correspond to those cells, not the whole column. In this case it would equal 00:30. I will appreciate your help. Order Procedure Drawn to Received EDCP 0:06 CBCsD 0:06 UA 0:16 EDCP 0:59 CBCsD 0:59 Troponin-I 0:59 EDCP 0:25 -- Clari |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average with specific criteria
XL2007
=AVERAGEIF(A2:A8,"EDCP",B2:B8) just press enter All versions =AVERAGE(IF(A2:A8="EDCP",B2:B8)) ctrl+shift+enter, not just enter "Clari" wrote: I need to average the cells on column b that correspond to the criteria in column a. If cells in column a match "EDCP", average the cells in column b that correspond to those cells, not the whole column. In this case it would equal 00:30. I will appreciate your help. Order Procedure Drawn to Received EDCP 0:06 CBCsD 0:06 UA 0:16 EDCP 0:59 CBCsD 0:59 Troponin-I 0:59 EDCP 0:25 -- Clari |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average with specific criteria
SUMIF(A:A,"EDCP",B:B)/COUNTIF(A:A,"EDCP")
-- pleae click yes if it was helpfull regards from Brazil Marcelo "Clari" escreveu: I need to average the cells on column b that correspond to the criteria in column a. If cells in column a match "EDCP", average the cells in column b that correspond to those cells, not the whole column. In this case it would equal 00:30. I will appreciate your help. Order Procedure Drawn to Received EDCP 0:06 CBCsD 0:06 UA 0:16 EDCP 0:59 CBCsD 0:59 Troponin-I 0:59 EDCP 0:25 -- Clari |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average with specific criteria
Hi,
and a non-array 2003 version formula: =SUMPRODUCT(--(A:A=D1),B:B/COUNTIF(A:A,D1)) where D1 contain EDCP -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Clari" wrote: I need to average the cells on column b that correspond to the criteria in column a. If cells in column a match "EDCP", average the cells in column b that correspond to those cells, not the whole column. In this case it would equal 00:30. I will appreciate your help. Order Procedure Drawn to Received EDCP 0:06 CBCsD 0:06 UA 0:16 EDCP 0:59 CBCsD 0:59 Troponin-I 0:59 EDCP 0:25 -- Clari |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average with specific criteria
Thanks for all the very helpful responses. You are all great!
-- Clari "Shane Devenshire" wrote: Hi, and a non-array 2003 version formula: =SUMPRODUCT(--(A:A=D1),B:B/COUNTIF(A:A,D1)) where D1 contain EDCP -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Clari" wrote: I need to average the cells on column b that correspond to the criteria in column a. If cells in column a match "EDCP", average the cells in column b that correspond to those cells, not the whole column. In this case it would equal 00:30. I will appreciate your help. Order Procedure Drawn to Received EDCP 0:06 CBCsD 0:06 UA 0:16 EDCP 0:59 CBCsD 0:59 Troponin-I 0:59 EDCP 0:25 -- Clari |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average with specific criteria
=SUMPRODUCT(--(A:A=D1),B:B/COUNTIF(A:A,D1))
You can not use a whole column prior to xl-2007 "Shane Devenshire" wrote: Hi, and a non-array 2003 version formula: =SUMPRODUCT(--(A:A=D1),B:B/COUNTIF(A:A,D1)) where D1 contain EDCP -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Clari" wrote: I need to average the cells on column b that correspond to the criteria in column a. If cells in column a match "EDCP", average the cells in column b that correspond to those cells, not the whole column. In this case it would equal 00:30. I will appreciate your help. Order Procedure Drawn to Received EDCP 0:06 CBCsD 0:06 UA 0:16 EDCP 0:59 CBCsD 0:59 Troponin-I 0:59 EDCP 0:25 -- Clari |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average of 12 months data from a specific month | Excel Discussion (Misc queries) | |||
| average with 2 criteria | Excel Worksheet Functions | |||
| Calculate Average on specific items | Excel Worksheet Functions | |||
| Average Age of Population on a Specific Date | Excel Worksheet Functions | |||
| Average only with specific critera | Excel Discussion (Misc queries) | |||
Linear Mode
