Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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!
    Regards,
    Rudi

Posting Permissions

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