Average of values that meet a condition

Topic

New Reply

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

Reply | Quote

Replies

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

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

Reply | Quote

Just what I needed .

Thank you!!!

Reply | Quote

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 http://www.cpearson.com/excel/array.htm

Reply | Quote

Excellent help!!!

My question was more oriented towards the use of arrays.

Thank you

Guillermo

Reply | Quote