# Average the smallest 3 values of the last 5 values, excluding blank cells

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})),"")} 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
)
)
)
``````
• It works perfectly if there is 5 or more values, but if there is only 3 values, it gives a false answer. I would like it to be blank if there is less than 5 values. Thanks for your help! Aug 20 at 3:21
• Please ask only one question per post. Making an exception this time because it is your first question. Edited the answer. Aug 20 at 9:47
• Aug 20 at 9:47