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.

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.

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.

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.

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.

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.

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.