Forum Discussion
Solved
Using AVERAGEIF to calculate cells > 0
Good morning,
Help!
I have a workbook that I want to find the average in a range IF the cell is >0, the hitch is that it's a MULTIPLE range selection, as in the example below
=AVERAGEIF((AH7,AH9,AH11),">0")
TIA!
AVERAGEIF won't work, for it expects a contiguous range.
You might use formulas to place the values that you want to average together in a contiguous range.
Otherwise, you have to use rather tricky formulas such as
=AVERAGE(IF((AH7:AH11>0)*ISODD(ROW(AH7:AH11)),AH7:AH11))
(If you don't have Microsoft 365 or Office 2021, you probably have to confirm the formula with Ctrl+Shift+Enter)
AVERAGEIF won't work, for it expects a contiguous range.
You might use formulas to place the values that you want to average together in a contiguous range.
Otherwise, you have to use rather tricky formulas such as
=AVERAGE(IF((AH7:AH11>0)*ISODD(ROW(AH7:AH11)),AH7:AH11))
(If you don't have Microsoft 365 or Office 2021, you probably have to confirm the formula with Ctrl+Shift+Enter)
- Wow, you guys are great! Works perfectly, only need to update the 3 ranges.
Thanks Hans alternatively maybe:
=AVERAGE(IFERROR(1/(1/INDEX(AH7:AH11,{1,3,5})),""))
this give more flexibility on which rows to include from the list {1,3,5} could be changed easily but only good to exclude only =0or better with 365:
=LET(in,INDEX(AH7:AH11,{1,3,5}), AVERAGE(FILTER(in,in>0)))
- Thanks Matt, tried your formula as well & it worked. Appreciate your responses!
