How to Average in Excel: AVERAGE, AVERAGEIF, Weighted, Trimmed and More

Most spreadsheets eventually need a single number that summarizes a column of values. That's where averaging earns its keep. Excel ships with a stack of functions that look almost identical at first glance — AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS, TRIMMEAN, MEDIAN, MODE.SNGL, GEOMEAN, HARMEAN — and each one quietly answers a different question. Pick the wrong one, and your report drifts in ways that nobody catches until it's printed.

This guide walks through every practical way to average in Excel. You'll see the plain arithmetic mean first, then conditional averages, weighted averages, trimmed means, geometric and harmonic means, rolling windows, and the status-bar trick that returns an answer without writing a formula at all. Examples use simple ranges like A1:A10 so you can drop them straight into a worksheet and replace the references with your own.

Wherever it matters, we'll call out the edge cases — blank cells, text values, zeros, errors, hidden rows — because those are the places averages quietly go wrong. By the end you'll know which function fits which situation, and the differences between mean, median and mode will stop being trivia. Excel has been doing this since the early 1990s, but the modern dynamic-array engine in Microsoft 365 changes a few of the older patterns. We'll cover both.

A short note before we start. Averaging is one of those tasks that seems simple until you watch ten people approach the same dataset and produce six different numbers. The functions aren't broken. The data isn't broken. It's the assumptions baked into the choice of function that diverge — and that's exactly what this guide is built to surface.

The everyday tool is the AVERAGE function. Type =AVERAGE(A1:A10) into any cell, and Excel adds up the numeric values in that range and divides by how many it counted. Blank cells are skipped. Text values are skipped. Logical values like TRUE and FALSE, when entered directly as arguments, are skipped too — but if they sit inside a referenced range, they're ignored. Errors propagate: one #DIV/0! in the range and the whole formula returns an error.

You can pass individual cells, multiple ranges, or hard-coded numbers — =AVERAGE(A1,B2,C3:C10,42) works just fine. Excel will average every numeric argument together as if they were one big pile. The count it divides by is the count of numeric values it actually found, not the count of cells you handed it.

What about cells that contain numbers formatted as text? Those get skipped, which is the source of many quiet reporting bugs. If a column was imported from a CSV and the values look like numbers but live as strings, your average will silently exclude them. The fix is to convert the column with VALUE() or by multiplying by 1, then average again. A quick way to spot the problem: check the count returned by =COUNT(A1:A10) against your expectation. If it's lower than the row count, you've got text-as-numbers hiding in there.

AVERAGEA is the sibling that nobody asks for until they need it. It works exactly like AVERAGE except text values count as zero and logical TRUE counts as 1, FALSE as 0. So =AVERAGEA(A1:A10) on a range that has eight numbers and two cells containing the word "n/a" will treat those n/a entries as zeros.

The denominator becomes 10, not 8, and the result drops. That's the right behavior when you want incomplete or unknown values to drag the mean down on purpose — say, when grading a quiz where missing answers count against the student. It's the wrong behavior when text means "no data, skip me."

Knowing which to use is half the battle. If your dataset uses blank cells for missing values, AVERAGE handles it correctly. If your dataset uses words like "N/A" or "missing", you have to decide whether those should be zero or excluded — and pick the function that matches. Mixing the two functions across reports without thinking is how two analysts looking at the same spreadsheet end up with two different averages. The behavior is documented but easy to forget under deadline pressure.

A useful sanity check: drop =COUNT(range) and =COUNTA(range) side by side next to your average. COUNT only sees numbers. COUNTA sees anything non-empty. If the two differ, you've got text mixed into your numeric range — and now you know whether to switch to AVERAGEA, clean the data, or wrap with AVERAGEIF to exclude the text values explicitly. That five-second check has saved more analysts than any formula has.

Conditional averaging is where Excel earns the spreadsheet-software label. AVERAGEIF averages only the values that meet a criterion. The syntax is =AVERAGEIF(range, criteria, [average_range]). To average all values in column A that are greater than 10, write =AVERAGEIF(A:A,">10"). To average values in column B where the matching cell in column A says "East", write =AVERAGEIF(A:A,"East",B:B). The criterion can be a number, a comparison, a text match with wildcards ("*south*"), or a cell reference holding any of those.

One of the most useful AVERAGEIF patterns ignores zeros. By default, zero is a number, so AVERAGE includes it — which is often wrong when zero means "didn't happen yet" rather than "happened, value of zero." The fix is =AVERAGEIF(A:A,"<>0"), which averages everything except the zeros. Another common pattern excludes a specific error: =AVERAGEIF(A:A,"<>#DIV/0!") would skip the divide-by-zero cells, although in practice it's cleaner to wrap the underlying formula in IFERROR so the errors never reach the range.

When you need multiple conditions stacked together, reach for AVERAGEIFS. It accepts criteria pairs: =AVERAGEIFS(C:C, A:A, "East", B:B, ">1000") averages column C for rows where region is East and sales exceed 1,000. There's no practical limit to the number of conditions, and they're all combined with AND. For OR-style logic, you typically build separate AVERAGEIFS and combine them — or switch to a dynamic-array approach using FILTER.

The weighted average is the one most people get wrong. The arithmetic mean treats every value equally — a 95 in a 10% quiz pulls the same weight as a 95 in a 60% final. That's almost never what teachers, accountants, or analysts actually want. The fix is to multiply each value by its weight, sum those products, and divide by the sum of the weights.

The cleanest Excel pattern uses SUMPRODUCT: =SUMPRODUCT(A2:A10, B2:B10) / SUM(B2:B10), where column A holds the values and column B holds the weights. SUMPRODUCT multiplies the two arrays element by element and adds them all up in one step. Divide by the sum of weights and you have a properly weighted average.

A real-world example: average price per share across multiple trade lots. If lot 1 was 100 shares at $42 and lot 2 was 250 shares at $46, the simple average ($44) is wrong because lot 2 is bigger. The weighted average =SUMPRODUCT(prices, shares)/SUM(shares) returns $44.86 — the actual cost basis. The same pattern works for grade weights, portfolio returns, customer lifetime values, or any aggregate where some inputs matter more than others.

If your weights already sum to 1 (or 100%), you can skip the division — =SUMPRODUCT(A2:A10, B2:B10) alone is the weighted mean. Just make sure the weights actually do sum to 1, because Excel won't warn you if they don't.

A practical follow-up: anyone working on grades or weighted portfolios should also know how to calculate weighted average in excel as a standalone task, because the SUMPRODUCT pattern fits neatly into named ranges and Excel Tables for repeated use. Once you've named the values and the weights, the formula becomes =SUMPRODUCT(Values, Weights)/SUM(Weights) and it survives new rows being added without any maintenance. That's the difference between a one-off calculation and a report you can trust next quarter.

Sometimes the regular mean is the wrong tool because outliers warp it. One CEO salary in a list of office workers can pull the company average up by tens of thousands of dollars. The trimmed mean handles this by chopping off the extremes before averaging. Excel's TRIMMEAN function does it in one step: =TRIMMEAN(A1:A10, 0.2) removes the top 10% and the bottom 10% of the values (20% total, split evenly) and averages what's left.

The second argument is the fraction to trim. 0.1 trims 5% from each end, 0.4 trims 20% from each end. Excel rounds the number of trimmed values down to the nearest even integer so it can split them symmetrically. With ten values and a 0.2 trim, it removes exactly one value from each end and averages the remaining eight. Trimmed means are standard in Olympic scoring, in survey data, and anywhere a few wild values would otherwise dominate.

The MEDIAN function is the related cousin — it returns the middle value rather than averaging anything. =MEDIAN(A1:A10) finds the value with as many entries above it as below. It's completely immune to outliers, which makes it the right choice when the distribution is skewed. Average household income and median household income differ by tens of thousands in many countries; the median is the more honest summary. Use median when half-above and half-below is the question; use mean when you actually want a sum-divided-by-count.

MODE.SNGL returns the most frequently occurring value in a range — =MODE.SNGL(A1:A10). If there's a tie, Excel returns the first one it encounters. MODE.MULT returns all of them as an array, which is handy in modern Excel where arrays spill automatically. Mode is rarely the right summary for continuous numeric data — two prices of $19.99 in a list of fifty unique prices doesn't tell you much — but for survey responses, T-shirt sizes, or any categorical data with repeats, it's exactly what you want.

GEOMEAN is the geometric mean: the nth root of the product of n positive numbers. Use it for averaging rates of change, growth rates, or any series where multiplication makes more sense than addition. If an investment grew 10%, then 20%, then -5%, the arithmetic mean of those rates is 8.33% — but the actual compound annual growth is closer to 7.85%, which is what =GEOMEAN({1.10, 1.20, 0.95})-1 returns. The function requires all positive numbers; convert percentage changes to multipliers (10% becomes 1.10) before passing them in, then subtract 1 from the result.

HARMEAN is the harmonic mean — the reciprocal of the average of the reciprocals. It's the right tool for averaging rates that share a common numerator. Average miles-per-hour across two equal-distance legs is a classic example: drive 60 miles at 30 mph then 60 miles at 60 mph, and your average speed is the harmonic mean of 30 and 60, which is 40 mph, not the arithmetic 45. Use =HARMEAN(A1:A10) for price-to-earnings averages across an index, fleet fuel economy, or any rate-based aggregate.

A quick sanity rule: for any positive numbers, harmonic mean ≤ geometric mean ≤ arithmetic mean. If you compute all three on the same dataset and they're identical, every value is the same. If they're radically different, the data is highly skewed — which is itself useful information about what's actually in the column. Most analysts don't think about which mean to use, so simply asking the question puts you ahead of the room.

Rolling or running averages smooth out noisy data. The classic technique: pick a window size — say 7 days — and average the most recent 7 values, then slide the window forward one row at a time. Older versions of Excel handled this with OFFSET: =AVERAGE(OFFSET(A1, COUNT(A:A)-7, 0, 7, 1)) averages the last 7 values in column A no matter where the data ends. The formula references A1 as an anchor, then offsets down by the count-minus-7, takes a 7-row tall and 1-column wide range, and averages that.

In Excel 365 and Excel 2024 with dynamic arrays, you can write cleaner versions using TAKE and FILTER. =AVERAGE(TAKE(A:A, -7)) takes the last 7 cells of column A and averages them — much easier to read than the OFFSET version. For a true rolling window that spills across multiple rows, combine SEQUENCE with AVERAGEIFS or use SCAN with a lambda function. The lambda approach is overkill for casual use, but it's the cleanest pattern when you need a full series of rolling averages.

Pivot tables handle averages without any formula writing. Drop a numeric field into the Values area, then click the dropdown arrow on the field name and choose Value Field Settings — switch from Sum to Average. The pivot table averages by every grouping you've placed in Rows and Columns. It's the fastest way to get averages by region, by month, by product category, all at once. The only catch: pivot tables can't show conditional averages with criteria you choose on the fly — for that you're back to AVERAGEIFS.

Conditional formatting based on the average highlights which rows are above or below the mean. Select the range, open Home → Conditional Formatting → Top/Bottom Rules → Above Average. Excel calculates the average and shades cells that exceed it. The same menu has Below Average. For more control, use a formula-based rule: New Rule → Use a formula → =A1>AVERAGE($A$1:$A$100). That's the same idea but you control the comparison and the formatting.

For per-column averages in Excel 365, dynamic arrays make life easy. =BYCOL(A1:E10, LAMBDA(col, AVERAGE(col))) returns one average per column, spilling into a horizontal array. The older approach was to write five separate AVERAGE formulas, one per column. BYCOL collapses that into a single formula that adapts when the range grows. BYROW does the same thing across rows. Both functions accept any aggregating lambda — swap AVERAGE for MEDIAN, GEOMEAN, or your own LAMBDA, and the same one-line pattern works.

The status bar at the bottom of the Excel window shows a quick average without writing any formula at all. Highlight any range of cells, then look at the bottom-right of the window — Excel shows Count, Sum, and Average by default. Right-click the status bar to add Min, Max, Numerical Count, or others. It's the fastest sanity check there is. When a coworker asks "what's the average?" you don't need a formula — just select the range and read the answer.