Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to Create Area Chart in Excel
  • How To Insert and Customize Sparklines in Excel
  • How to Sort by Color in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • Get column index in Excel Table

References

  • How to use Excel MMULT Function
  • How to use Excel INDIRECT Function
  • How to get last row in numeric data in Excel
  • How to get relative row numbers in a range in Excel
  • How to use Excel COLUMN Function

Data Validations

  • Excel Data validation whole percentage only
  • Data validation must not exist in list
  • Excel Data validation allow weekday only
  • Excel Data validation unique values only
  • Excel Data validation allow uppercase only

Tag: AVERAGE function

AVERAGE function: Description, Usage, Syntax, Examples and Explanation

by

What is AVERAGE function in Excel? MODE.SNGL function is one of Statistical functions in Microsoft Excel that returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers. Syntax of AVERAGE function AVERAGE(number1, [number2], …) The AVERAGE function syntax has the following arguments: Number1    Required. …

Continue Reading

Sum range with INDEX in Excel

by

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 …

Continue Reading

Calculate Weighted Average in Excel

by

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 …

Continue Reading

3D sum multiple worksheets in Excel

by

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 …

Continue Reading

Average last 5 values in Excel

by

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, …

Continue Reading

Average last 5 values in columns in Excel

by

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 …

Continue Reading

Average numbers in Excel

by

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 …

Continue Reading

Average numbers ignore zero in Excel

by

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 …

Continue Reading

Average the last 3 numeric values in Excel

by

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 …

Continue Reading

Average top 3 scores in Excel

by

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 …

Continue Reading

9 Mathematical Computations Example using Excel Statistical Function

by

In addition to formulas, another way to conduct mathematical computations in Excel is through functions. Statistical functions apply a mathematical process to a group of cells in a worksheet. This chapter gives an overview of some very useful statistical functions like: AVERAGE function, AVERAGEIF function, MEDIAN function, SUMIF function, MIN function, MODE function, STEDV function, LARGE function, SMALL function. Average To calculate the average of a …

Continue Reading

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

by

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 …

Continue Reading

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use Excel AND Function
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • Check multiple cells are equal in Excel
  • IFS function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Get month from date in Excel
  • DATEDIF function: Description, Usage, Syntax, Examples and Explanation
  • Get first Monday before any date in Excel
  • Display the current date in Excel
  • Calculate number of hours between two times in Excel

Grouping

  • Group numbers at uneven intervals in Excel
  • Categorize text with keywords in Excel
  • Calculate conditional mode with criteria in Excel
  • Running count group by n size in Excel
  • Group times into unequal buckets in Excel

General

  • How to generate random date between two dates in Excel
  • Sum by group in Excel
  • List worksheet index numbers in Excel
  • How to calculate percent change in Excel
  • How to choose page/paper size in Excel before Printing
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning