Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Average of values that meet a condition

    I want to calculate the average values in a column that meet a certain condition (i.e. >1).

    What is the function or array that I should use to calculate the average (or any other statistical parameter)?

    Thank you

    Guillermo
    Guillermo

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average of values that meet a condition

    If the values are in A1:A100, then use:

    <pre>=SUMIF(A1:A100,">1",A1:A100)/COUNTIF(A1:A100,">1")
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average of values that meet a condition

    Just what I needed .

    Thank you!!!
    <img src=/S/cool.gif border=0 alt=cool width=15 height=15>
    Guillermo

  4. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average of values that meet a condition

    Or, alternatively

    =AVERAGE(IF(A1:A10>1,A1:A10)) entered as an array formula

    + Shorter
    + Allows multiple/nested conditions
    - If used a lot, slows down recalculation
    - Array entry i.e. Ctrl+Shift+Enter needs get used to

    See also <A target="_blank" HREF=http://www.cpearson.com/excel/array.htm>http://www.cpearson.com/excel/array.htm</A>

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average of values that meet a condition

    Excellent help!!!

    My question was more oriented towards the use of arrays.

    Thank you

    Guillermo

    <img src=/S/cool.gif border=0 alt=cool width=15 height=15>
    Guillermo

Posting Permissions

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