Forum Discussion
Solved
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 rep...
Press the Ctrl + Shift + Enter keys simultaneously
=AVERAGE(IF(A2:H2=1,OFFSET(A2:H2,0,1),""))
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.