If statement to calculate an average

[rhudgins]

I would like to write a IF statement that says if a value in Column Q is greater then 5%, then calculate for each column (R:AQ) the average of those rows with a value greater than 5% in Column Q.

[NBVC]

Do you mean?

In Q18:

=AVERAGE(IF($Q$2:$Q$13>5%,Q$2:Q$13))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied across all columns.

[rhudgins]

Thanks that works but is there a way to do it without CTRL+SHIFT+ENTER because when I hit F2 on the value it returns #VALUE

[NBVC]

Try:

=SUMIF($Q$2:$Q$13,">5%",Q$2:Q$13)/COUNTIF($Q$2:$Q$13,">5%")

[rhudgins]

The second formula is much better but instead of having 5% embedded in the formula I would like to have it in a seperate cell so that I can change it to 4%, 3%, 2% or what ever other number I chose. When I try to replace the ">5%" with ">$M$17$" the formula will not work. Can this be fixed? Thanks so much.

[NBVC]

Try

=SUMIF($Q$2:$Q$13,">"&$M$17,Q$2:Q$13)/COUNTIF($Q$2:$Q$13,">"&$M$17)

[rhudgins]

Perfect. Thanks for all of your help!