AVERAGE function in Excel

How to calculate average last N values in a table in Excel

To calculate the average for the last N values n an Excel table (i.e. last 3 rows, last 5 rows, etc.) you can use the AVERAGE function together with the INDEX and ROWS functions. See example below: Formula =AVERAGE(INDEX(table[column],ROWS(table)-(N-1)):INDEX(table[column],ROWS(table))) Explanation In the example shown, the formula in F5 is: =AVERAGE(INDEX(Table1[Sales],ROWS(Table1)-(F4-1)):INDEX(Table1[Sales],ROWS(Table1))) How this formula works This… read more »

Sum range with INDEX in Excel

This tutorial shows how to calculate Sum range with INDEX in Excel using the example below; Formula =SUM(INDEX(data,0,column)) Explanation To sum all values in a column or row, you can use the INDEX function to retrieve the values, and the SUM function to return the sum. This technique is useful in situations where the row or column being summed… read more »

Average top 3 scores in Excel

This tutorial shows how to work Average top 3 scores in Excel using the example below; Formula =AVERAGE(LARGE(range,{1,2,3})) Explanation To average the top 3 scores in a data set, you can use a formula based on the LARGE function. In the example shown, the formula in G6 is: =AVERAGE(LARGE(B6:F6,{1,2,3})) How this formula works The LARGE function… read more »

Average the last 3 numeric values in Excel

This tutorial shows how to work Average the last 3 numeric values in Excel using the example below; Formula {=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),{1,2,3}),ROW(data), data))} Explanation To average the last 3 numeric values in a range, you can use an array formula based on a combination of functions to feed the last n numeric values into the AVERAGE function. In the example… read more »

Average numbers ignore zero in Excel

This tutorial shows how to work Average numbers ignore zero in Excel using the example below; Formula =AVERAGEIF(range,”<>0″) Explanation To get the average of a set of numbers, excluding or ignoring zero values, use the AVERAGEIF function. In the example shown, the formula in E6 is: =AVERAGEIF(B6:D6,”<>0″) How this formula works AVERAGEIF performs an average based on… read more »

Average numbers in Excel

This tutorial shows how to Average numbers in Excel using the example below; Formula =AVERAGE(range) Explanation To get the average of a set of numbers, use the AVERAGE function. In the example shown, the formula in E5 is: =AVERAGE(B5:D5) which is then copied down the table. How this formula works AVERAGE is an automatic function… read more »

Average last 5 values in columns in Excel

This tutorial shows how to work Average last 5 values in columns in Excel using the example below; Formula =AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N)) Explanation To average the last 5 data values in a range of columns, you can use the AVERAGE function together with the COUNT and OFFSET functions. In the example shown, the formula in F6 is: =AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5)) How this… read more »

Average last 5 values in Excel

This tutorial shows how to work Average last 5 values in Excel using the example below; Formula =AVERAGE(OFFSET(A1,COUNT(A:A),0,-N)) Explanation To average the last 5 data points, you can use the AVERAGE function together with the COUNT and OFFSET functions. You can use this approach to average the last N data points: last 3 days, last 6 measurements,… read more »

3D sum multiple worksheets in Excel

This tutorial shows how to 3D sum multiple worksheets in Excel using the example below; Formula =SUM(Sheet1:Sheet3!A1) Explanation To sum the same range in one or more sheets, you can use the SUM formula with a special syntax called a “3d reference”. In the example shown, the formula in D6 is: =SUM(Week1:Week5!D6) How this formula… read more »

Calculate Weighted Average in Excel

To calculate a weighted average in Excel, simply use the SUMPRODUCT and the SUM function. 1. The AVERAGE function below calculates the normal average of three scores. Suppose your teacher says, “The test counts twice as much as the quiz and the final exam counts three times as much as the quiz”. 2. Below you can find the… read more »

Streamlined Topics