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

CodePudding user response：

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)),"")

CodePudding user response：

Which number are you checking? Nested formula: IF(Number>5,AVERAGE(C2,E2,H2),"")

CodePudding user response：

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

CodePudding user response：

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},D2,E2,H2),0))/INDEX(FREQUENCY(IFERROR(--CHOOSE({1,2,3},D2,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.