Forum Discussion
Average of nonsequential range if adjacent values meet some criteria...
Hi 🙂
I am trying to construct a formula that will allow me to find the average of a nonsequential range of values, given the value in the cell to their immediate left is 1 and not 2 (where 1 represents correct and 2 represents incorrect, for context)... That is:
| A | B | C | D | E | F | G | H | |
| 1 | response | confidence | response | confidence | response | confidence | response | confidence |
| 2 | 1 | 73 | 1 | 60 | 2 | 21 | 1 | 93 |
So, I'm looking to calculate the average "confidence" for only correct (1) responses, and ignoring incorrect (2) responses... any suggestions?
Press the Ctrl + Shift + Enter keys simultaneously
=AVERAGE(IF(A2:H2=1,OFFSET(A2:H2,0,1),""))
6 Replies
Since I have now moved completely to 365, I sometimes break the solution definition into two parts. The first picks meaningful arrays out of custom data layout (often using Lambda function), so the calculation becomes a simple second step.
= LET( k, {1;2;3;4}, response, INDEX(data, 2*k-1), confidence, INDEX(data, 2*k), filtered, FILTER(confidence, response=1), AVERAGE(filtered))This builds two column arrays 'response' and 'confidence' so that the calculation becomes a simple AVERAGE over the filtered array.
- ...
As variant
=SUMPRODUCT(ISODD(COLUMN(A2:G2))*(A2:G2=1)*B2:H2) / SUMPRODUCT(ISODD(COLUMN(A2:G2))*(A2:G2=1))
