Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to get address of named range in Excel
  • Merge tables with VLOOKUP in Excel
  • How to use Excel TRANSPOSE Function
  • Left Lookup in Excel
  • Two-way lookup with VLOOKUP in Excel

Data Analysis Examples

  • How to Create Column Chart in Excel
  • Remove Duplicates Example in Excel
  • Managing Conditional Formatting Rules in Excel
  • How to create dynamic reference table name in Excel
  • Get column name from index in Excel Table

Data Validation Examples

  • Excel Data validation date in specific year
  • Excel Data validation require unique number
  • Excel Data validation allow uppercase only
  • Excel Data validation must not contain
  • Excel Data validation don’t exceed total

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

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

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

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

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

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

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

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

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
  • Complete List of Excel Text Functions References and Examples
  • How to extract domain name from URL in Excel
  • How to compare two text strings in Excel
  • How to check if cell contains all of many things in Excel
  • DBCS function: Description, Usage, Syntax, Examples and Explanation
  • Add decimal hours to time in Excel
  • NETWORKDAYS.INTL function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate months between dates in Excel
  • Calculate series of dates by workdays in Excel
  • Calculate expiration date in Excel
  • CUMPRINC function: Description, Usage, Syntax, Examples and Explanation
  • Tax rate calculation with fixed base in Excel
  • AMORDEGRC function: Description, Usage, Syntax, Examples and Explanation
  • PDURATION function: Description, Usage, Syntax, Examples and Explanation
  • Future value vs. Present value examples in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles