How to Calculate the Average Top 3 Scores in Excel

The use of Microsoft Excel has grown considerably among school teachers. Educators are always looking for tools which would assist them in preparing student’ report cards with complete accuracy, and one measure is calculating the average top 3 scores. The following comprehensive guideline discusses that how a user can use Microsoft Excel and its functions for calculating the average top 3 scores out of an expanded list.

Finding Out Average Top 3 Scores Using Excel:

Figure1. Example of how to calculate the average top 3 scores on Excel

Formula for the average top three scores:

=AVERAGE(LARGE(range,{1,2,3}))

Explanation of the AVERAGE scores scenario:

On certain occasions, a user is often required to find out the top 3 scores using Microsoft Excel. It requires the to use of a formula, which is a derivative of the LARGE function on excel. A user can apply the formula in a column or in a row, and it will sum up with the average of top 3 values in the dataset. In other words, the formula will identify three of the top values in a row or column, and it would later calculate its mean value.

How does the AVERAGE formula work using the LARGE function?

A user is required to align the LARGE function with its corresponding elements for the completion of the formula. The LARGE function is calibrated to return a top value from a dataset. As you can see in the screenshot attached above—the LARGE formula is further used in combination with AVERAGE function, which would calculate the sum of three largest values, and it would be further divided by (3) for finding out its average value.

 

Figure 2. Example of calculating average top 3 scores in a row

In the example, we have applied the function row-wise. It is focused on calculating the average of the values from B6:F6. The values enclosed in the parenthesis {1,2,3} signify the top nth value, which would complete the formula for calculation. Let’s have a look at the following manual calculation:

=10+10+103303=10

Therefore, the average of the top 3 scores would be (10). In this particular case, a user is required to enter multiple values that meet the criteria of {1,2,3}. The constant array is later converted into LARGE as a secondary argument. The arrangement of the values results in returning a set of values, which are inclusive of the top three values. Then, the AVERAGE function works accordingly for estimating the average of the entered values.

When using the AVERAGE function, a user should allow it to calculate the array values automatically, and it should refrain from using (CSE) for specifying a formula in a cell block.

Similarly, the formula could be inserted into one cell block, and it could be dragged down a column or across a row for estimating the average top 3 scores of the values on an Excel Spreadsheet.

Figure 3. Example of calculating average top 3 scores in misc. row

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar