Average Numbers

Recommended Posts

Average Numbers

The SMALL function is what you need. Suppose a series of ten numbers falls in the range A1:A10. The formula =SMALL(A\$1:A\$10,1) returns the smallest, =SMALL(A\$1:A\$10,2) returns the second-smallest, and so on. As you might expect, the LARGE function returns the specified largest values in a range.

Averaging a series of numbers while omitting one or more of the smallest ones is a fairly common task. For example, teachers frequently discard the lowest one or two scores when grading students.

Start by naming the range containing the values. Select the range, click on the Name box to the left of the Formula box, and type the name (for example, MyRange). This formula will calculate the average of the numbers in the range, omitting the lowest two values:

=(SUM(MyRange)-SMALL(MyRange,1) -SMALL(MyRange,2))/(COUNT(MyRange) -2).

The formula first sums all the values in MyRange and then subtracts the two smallest. It divides the result by the count of items in MyRange minus 2. Of course, you can write this formula using an ordinary cell reference, like A1:A10, rather than a named range, but the range appears four times in the formula, which means you have four chances to mistype it. Naming the range makes writing the formula much easier.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account. ×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.