-1

I'm trying to average the smallest 3 values of the last 5 valves, excluding blank cells, in a row. I have a working Excel formula, but I'm not savvy with Sheets yet. Here is a screen shot of my Excel sheet. Excel formula: {=IFERROR(AVERAGE(SMALL(INDEX(2:2,AGGREGATE(14,6,COLUMN(D2:CL2)/(D2:CL2<>""),5)):CL2,{1,2,3})),"")}

Averages

1

1 Answer 1

0

To get the last five values in each row, use sortn(). To get the three smallest values among those five, use, query(), like this:

=average( 
  query( 
    sortn( transpose(K2:2), 5, 0, transpose(column(K2:2) * not(isblank(K2:2))), false ), 
    "order by Col1 limit 3", 0 
  ) 
)

To show a blank when there are less than five values in a row, use count() and if(), like this:

=if( 
  count(K2:2) < 5, 
  iferror(1/0), 
  average( 
    query( 
      sortn( transpose(K2:2), 5, 0, transpose(column(K2:2) * not(isblank(K2:2))), false ), 
      "order by Col1 limit 3", 0 
    ) 
  ) 
)
3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.