I have some values in a column which are not necesarily sorted. Then I want to find the average of the lowest n values from the column with the values.

column values

So let's take the example above. Say I need to average n=3 lowest values from the column above with values ranging from A1 to A10. The result will be the average of 1 (taken from A1), 2 (taken from A6) and 3 (taken from A2). I am thinking of something like AVERAGE(LOWEST_N(A1:A10, 3)) but I'm not sure it can be as simple as that.

Note: I can't sort the values on that specific column. I also do not wish to manually move the values to another column and sort them there, because I want to do this many multiple times and I need to automate the process.

2 Answers

1
SJR On Best Solutions

You can use an array formula, using ctrl, shift and enter to confirm.

=AVERAGE(SMALL(A1:A10,ROW(1:3)))
0
Community On

Try this: =AVERAGEIF(A1:A10, "<4")