How to calculate average in Excel: formula examples

This tutorial demonstrates various methods for calculating averages in Excel, including formula-based and formula-free approaches, with options for rounding results. Microsoft Excel offers several functions for averaging numerical data, and this guide covers them all. These methods are compatible with Excel versions 2007 and later, including Excel 365.

Understanding Averages

An average represents the typical value within a dataset. Mathematically, it's the sum of all values divided by the count of values.

Averaging Without Formulas

Excel's status bar instantly displays the average of selected cells, offering a quick way to find the average without using formulas. Simply select the cells and check the status bar at the bottom of the Excel window.

Manual Average Calculation

While less efficient than Excel functions, you can manually calculate the average using the SUM and COUNT functions: =SUM(range)/COUNT(range). This provides a way to verify formula results.

Using the AVERAGE Function

The AVERAGE function calculates the average of numbers in specified cells or ranges. It ignores text, empty cells, and logical values (TRUE/FALSE). Zero values are included. The syntax is AVERAGE(number1, [number2], ...)

Using the AVERAGEA Function

AVERAGEA includes all non-empty cells, treating text as 0, TRUE as 1, and FALSE as 0. Empty cells are ignored. The syntax is AVERAGEA(value1, [value2], ...)

Conditional Averaging with AVERAGEIF

AVERAGEIF averages cells meeting a specific criterion. The syntax is AVERAGEIF(range, criteria, [average_range]).

Multi-Criteria Averaging with AVERAGEIFS

AVERAGEIFS extends AVERAGEIF to handle multiple criteria. The syntax is AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

AVERAGE with OR Logic

To average cells based on an OR condition (at least one criterion is true), a custom array formula is needed: AVERAGE(IF(ISNUMBER(MATCH(range, {criteria1, criteria2,...}, 0)), average_range)). Remember to use Ctrl+Shift+Enter for array formulas in older Excel versions.

Rounding Averages

• Decrease Decimal: Use the "Decrease Decimal" button on the Home tab to reduce displayed decimal places without altering the underlying value.
• Format Cells: Adjust decimal places in the "Format Cells" dialog (Ctrl+1). This only affects the display.
• Rounding Functions: Use ROUND, ROUNDUP, or ROUNDDOWN to round the calculated average itself. For example: =ROUND(AVERAGE(range), 2) rounds to two decimal places.

Downloadable Practice Workbook:

[Calculate average in Excel - examples (.xlsx file)]

This enhanced response provides a more comprehensive and structured explanation of the various averaging methods in Excel, including clearer descriptions and improved formatting. The image captions have also been made more descriptive.

The above is the detailed content of How to calculate average in Excel: formula examples. For more information, please follow other related articles on the PHP Chinese website!