Excel averageif with specific cells

Question

basically what i would like to do is find the average of these 3 cells if the number is bigger than 5. So i tried averageif(c2,e2,h2,”>5”). But because these cells are not range the formula doesn’t work. Is there any advice for me to use different formula

Answer 1

AVERAGEIF doesn’t work on a non-contiguous range. You may try as shown below as well. This doesn't need confirmed CTRL SHIFT ENTER to press after entering the formula.

Formula used in cell I2

=IFERROR(SUMPRODUCT(--(CHOOSE({1,2,3},C2,E2,H2))*--(CHOOSE({1,2,3},C2,E2,H2)>5))/INDEX(FREQUENCY((C2,E2,H2),5),2),"")

One More Alternative:

=IFERROR(SUMPRODUCT(--(CHOOSE({1,2,3},C3,E3,H3))*--(CHOOSE({1,2,3},C3,E3,H3)>5))/SUMPRODUCT(--(CHOOSE({1,2,3},C3,E3,H3)>5)),"")

Answer 2

Another option to consider

=AVERAGE(IF(INDEX(C2:H2,,{1,3,6})>5,INDEX(C2:H2,,{1,3,6})))

Note ^^ this is an array function and (depending on your version) will need confirmed with ctrl+shift+enter not just enter.

If you have Microsoft 365 (Possibly also Excel 2021 though I can't test).

=LET(f,INDEX(C2:H2,,{1,3,6}),AVERAGE(IF(f>5,f)))

The dynamic calculation of M365 means you can just enter this in.

Answer 3

Any of the *IF(S) type formula do not like broken ranges. You will need to use a different formula that accepts arrays:

=AVERAGE(IF(CHOOSE({1,2,3},C2,E2,H2)>5,CHOOSE({1,2,3},C2,E2,H2)))

Depending on one's version this may need to be confirmed with Ctrl-Shift-Enter

Answer 4

Side Note:

AVERAGEIF Function doesn't work on a non-contiguous ranges therefore we need to use a separate formula, also if there is a TEXT in any one of the cells it needs to bypass the #DIV/0 error as well and gives us the required output.

So please refer below for the following two alternative formulas,

=AVERAGE(IFERROR(--CHOOSE({1,2,3},C2,E2,H2),""))

This second formula, has another advantage which the above formula doesn't, works when we need AVERAGE from Multiple sheet cells

=SUM(IFERROR(--CHOOSE({1,2,3},C2,E2,H2),0))/INDEX(FREQUENCY(IFERROR(--CHOOSE({1,2,3},C2,E2,H2),0),0),2)

The above formula is an array formula, so if you are not using Excel 2021 or O365 then you need to confirm press CTRL SHIFT ENTER.