1. ## Help with an AverageIf function please... (Excel XP)

Hi,
I have a column of numbers. I need a formula at the bottom to average out all numbers in the column that are greater than 5. I wanted to use SUMIF() / COUNTIF, but the range in sumif cannot be that same as the sumrange! Any suggestions on another function to do this?
Tx

2. ## Re: Help with an AverageIf function please... (Excel XP)

=SUMIF(A1:A100,">5") will sum all numbers in A1:A100 that are greater than 5. So you can use
<code>
=SUMIF(A1:A100,">5")/COUNTIF(A1:A100,">5")
</code>
Alternatively, you can use this array formula:
<code>
=AVERAGE(IF(A1:A100>5,A1:A100))</code>

3. ## Re: Help with an AverageIf function please... (Excel XP)

Thankyou for the quick response Hans. I notice that I was not putting the >5 criteria into quotes. This was causing the error I could not spot...
Cheers for the array formula too!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•