| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating Average IF
I need the following function:
I want the average of I3:I6 only for records where 03:06 = 18. Please help! Thanks in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating Average IF
umm...wouldn't the answer be 18 (or zero, if nothing meets criteria)?
=IF(COUNTIF(I3:I6,18)=0,"",SUMIF(I3:I6,18)/COUNTIF(I3:I6,18)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "AAA1986" wrote: I need the following function: I want the average of I3:I6 only for records where 03:06 = 18. Please help! Thanks in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating Average IF
If you meant not 03:06 but O3:O6, your formula would be
=SUMPRODUCT(--(O3:O6=18),I3:I6)/COUNTIF(O3:O6,18) -- David Biddulph "AAA1986" wrote in message ... I need the following function: I want the average of I3:I6 only for records where 03:06 = 18. Please help! Thanks in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating Average IF
=IF(COUNTIF(I3:I6,18)=0,"",SUMIF(I3:I6,18)/COUNTIF(I3:I6,18))
simplify version: =IF(COUNTIF(I3:I6,18),18,"") "Luke M" wrote: umm...wouldn't the answer be 18 (or zero, if nothing meets criteria)? =IF(COUNTIF(I3:I6,18)=0,"",SUMIF(I3:I6,18)/COUNTIF(I3:I6,18)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "AAA1986" wrote: I need the following function: I want the average of I3:I6 only for records where 03:06 = 18. Please help! Thanks in advance. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating Average IF
No, let me try again. To simplify: for all records that equal "18" in column
B, I want the average of column A. For example: A B 1 4 18 2 2 17 3 4 18 In the above table, I would only want the averages of A1 and A3 because both rows have "18" in the B column. The average here would be 4. Thanks. "Luke M" wrote: umm...wouldn't the answer be 18 (or zero, if nothing meets criteria)? =IF(COUNTIF(I3:I6,18)=0,"",SUMIF(I3:I6,18)/COUNTIF(I3:I6,18)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "AAA1986" wrote: I need the following function: I want the average of I3:I6 only for records where 03:06 = 18. Please help! Thanks in advance. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating Average IF
=SUMIF($B$1:$B$3,"=18",$A$1:$A$3)/COUNTIF($B$1:$B$3,"=18")
"AAA1986" wrote: No, let me try again. To simplify: for all records that equal "18" in column B, I want the average of column A. For example: A B 1 4 18 2 2 17 3 4 18 In the above table, I would only want the averages of A1 and A3 because both rows have "18" in the B column. The average here would be 4. Thanks. "Luke M" wrote: umm...wouldn't the answer be 18 (or zero, if nothing meets criteria)? =IF(COUNTIF(I3:I6,18)=0,"",SUMIF(I3:I6,18)/COUNTIF(I3:I6,18)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "AAA1986" wrote: I need the following function: I want the average of I3:I6 only for records where 03:06 = 18. Please help! Thanks in advance. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating Average IF
Perfect! Thank you.
"Smoking" wrote: =SUMIF($B$1:$B$3,"=18",$A$1:$A$3)/COUNTIF($B$1:$B$3,"=18") "AAA1986" wrote: No, let me try again. To simplify: for all records that equal "18" in column B, I want the average of column A. For example: A B 1 4 18 2 2 17 3 4 18 In the above table, I would only want the averages of A1 and A3 because both rows have "18" in the B column. The average here would be 4. Thanks. "Luke M" wrote: umm...wouldn't the answer be 18 (or zero, if nothing meets criteria)? =IF(COUNTIF(I3:I6,18)=0,"",SUMIF(I3:I6,18)/COUNTIF(I3:I6,18)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "AAA1986" wrote: I need the following function: I want the average of I3:I6 only for records where 03:06 = 18. Please help! Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating If Statement not to include if O in average | Excel Discussion (Misc queries) | |||
| creating a running average | New Users to Excel | |||
| Creating Average Line on chart | Charts and Charting in Excel | |||
| creating an average? | Excel Discussion (Misc queries) | |||
| help creating average formula | Excel Discussion (Misc queries) | |||
Linear Mode
