Excel Average for Every 5-Unit X-Axis Range

Charts & pivot tables · Updated November 19, 2026

To find the average Y value in each 5-unit X-axis range in Excel, put the X values in A2:A100, the Y values in B2:B100, and each bin’s lower and upper limits in D and E; in F2, enter =AVERAGEIFS($B$2:$B$100,$A$2:$A$100,">="&D2,$A$2:$A$100,"<"&E2) and copy it down. With limits 0 and 5, this averages Y for rows where X is at least 0 and less than 5; the next bin can be 5 to less than 10.

What “every 5 on the X-axis” means

This calculation is usually a grouped average: take observations whose X values fall inside a five-unit interval, then average their corresponding Y values. For example, if X represents time in seconds and Y represents measured temperature, the result might be one average temperature for 0–less than 5 seconds, another for 5–less than 10 seconds, and so on. The X values decide which group each row belongs to; the Y values are what get averaged.

That is different from taking the average of every five rows. If your data are sorted and you want rows 1–5, 6–10, and so forth, you need row-based groups instead. It is also different from averaging the X values themselves. Being clear about which meaning you want prevents a plausible-looking but incorrect result.

Arrange the source data and bin limits

Use one row per observation, with numeric X and Y values in matching rows. A practical layout is:

  • Column A: X value, such as time, distance, or another horizontal-axis measurement.
  • Column B: the Y value to average for that observation.
  • Column D: the lower limit of each interval.
  • Column E: the upper limit of each interval.
  • Column F: the resulting average for that interval.

For example, enter 0 and 5 in D2 and E2, then 5 and 10 in D3 and E3. Continue with 10 and 15 in the next row. The intervals do not have to start at zero: if the useful range begins at 2, use 2–7, 7–12, and so on. Choose limits that make sense for the units and the question you are investigating.

Keep the X and Y ranges aligned. If the X range is A2:A100, the corresponding Y range should be B2:B100. Including a header in one range but not the other, or ending the ranges on different rows, can cause incorrect calculations or errors. Convert the source data to an Excel Table if it will grow over time; structured references can make formulas easier to maintain, although the ordinary cell-range formula is straightforward for a fixed dataset.

Use AVERAGEIFS for each interval

With X values in A2:A100, Y values in B2:B100, and the current interval’s lower and upper limits in D2 and E2, enter this in F2:

=AVERAGEIFS($B$2:$B$100,$A$2:$A$100,">="&D2,$A$2:$A$100,"<"&E2)

AVERAGEIFS averages the first range, B2:B100, only for rows meeting the conditions applied to the criteria range, A2:A100. The first condition requires X to be greater than or equal to the lower limit in D2. The second requires X to be less than the upper limit in E2. The dollar signs keep the source ranges fixed when you copy the formula, while D2 and E2 adjust to each bin’s limits.

After entering the formula, copy F2 down alongside the remaining bin limits. In Microsoft 365 for Windows, you can drag the fill handle at the lower-right corner of the selected cell, or copy and paste into the cells below. The same formula and fill-handle approach work in Excel for Mac and Excel for the web; no special version-specific function is required.

Worked example and result

Suppose the source data contain these pairs: (1, 10), (2, 20), (4, 30), (5, 40), (7, 50), and (9, 60), where each pair is (X, Y). Set up two intervals: 0 to less than 5, and 5 to less than 10. The first group includes the Y values 10, 20, and 30, whose average is 20. The second contains 40, 50, and 60, whose average is 50.

The X value of exactly 5 belongs to the second interval, not the first, because the first interval’s upper limit is exclusive. This convention makes adjacent bins meet without overlapping: a boundary value cannot be counted twice. You can verify a result by filtering the source X values to the interval and calculating the mean of the matching Y values independently.

If an interval contains Y values 10 and 30, its average is 20, regardless of how far apart their X values are within that band. This is an ordinary arithmetic mean of the observations in the bin; it is not weighted by the distances between X values. That distinction matters when measurements are irregularly spaced. If a time-weighted or distance-weighted average is needed, a simple AVERAGEIFS result may not represent the quantity you intend.

Choose boundaries and handle the last bin

Using lower-inclusive and upper-exclusive intervals—such as 0 to less than 5, then 5 to less than 10—is a reliable default. It assigns every boundary value to exactly one group and works for decimal X values as well as integers. An X value of 4.99 belongs to the first example interval, while 5 belongs to the next.

For whole-number data, some readers instead describe bins as 0–4, 5–9, and 10–14. These are equivalent to 0 to less than 5, 5 to less than 10, and 10 to less than 15 only when the values are integers. If X can contain decimals, “0–4” is ambiguous: it might accidentally exclude values such as 4.5. Prefer explicit interval rules in your worksheet labels or documentation.

Decide how to treat the final endpoint. If the last interval is 10 to less than 15, a value of exactly 15 belongs in a later interval and will not appear if no later interval is defined. If you want the final interval to include the maximum endpoint, make that final upper-bound condition inclusive. Do not make every interval’s upper bound inclusive, because adjacent groups would then double-count values such as 5.

Also decide what to do with X values below the first lower limit or at or above the final upper limit. They are excluded by the bins shown. Add more intervals, widen the range, or identify those observations separately rather than assuming they have been averaged.

Turn the grouped averages into a chart

The averages can be plotted once each interval has a result. A useful chart often places the average Y on the vertical axis and the midpoint of each X interval on the horizontal axis. For bins 0–5 and 5–10, those midpoints are 2.5 and 7.5. The midpoint is a representative location for the interval, not an observed X value and not necessarily the average X value of observations in that bin.

To calculate the midpoint, with the bin’s lower limit in D2 and upper limit in E2, enter =AVERAGE(D2:E2) in a separate column and copy it down. Create an XY (Scatter) chart using that midpoint column as X values and the AVERAGEIFS results as Y values. A scatter chart treats horizontal positions as numbers, which is useful for numeric intervals. A Line chart uses a category axis, so spacing between labels may look uniform even when numeric distances are not.

Keep the bin width and axis units visible in chart labels or a note. A chart with averages alone hides how many observations contributed to each point: one bin might contain many measurements and another only one. Consider showing counts in a separate column or adding them to a companion chart, especially when readers might interpret every average as equally reliable.

Common problems and checks

  • #DIV/0!: No numeric Y values met the interval criteria, so Excel has nothing to average. Check the limits and X data, or leave the result visible as an indication that the bin is empty.
  • Unexpectedly empty groups: Confirm that X values are numeric rather than numbers stored as text, and check that the lower limit is less than the upper limit.
  • Boundary values counted in the wrong place: Remember that the formula uses greater than or equal to the lower limit and less than the upper limit. A value equal to the upper limit moves to the next bin.
  • Incorrect averages: Ensure the average range is the Y column and both criteria ranges are the X column. AVERAGEIFS ignores blank cells in the average range, but a numeric zero is a real value and is included.
  • Formula errors from range sizes: Make sure the average range and each criteria range cover the same number of rows. Check for error values in the source data if a result unexpectedly returns an error.

For a quick diagnostic, count the records in each interval as well as averaging them. If a bin has no matching X values, the count will be zero and an average cannot be calculated. If you need to distinguish an empty interval from an actual average of zero, do not replace all errors with zero without also recording the count; that would make missing data look like a measured result.

Alternative: group the data with a PivotTable

A PivotTable can be convenient when you want to explore several summaries or change the grouping interactively. Create a PivotTable from the source data, place the numeric X field in Rows and the Y field in Values, then set the value field’s summary calculation to Average. In Excel for Microsoft 365 on Windows, right-click a numeric row label and choose Group; set the interval size in the grouping dialog to 5. The precise grouping choices depend on the field and data, and the source X values need to be suitable numeric values.

PivotTable grouping is useful for quick analysis, but it behaves differently from a formula grid. The bin labels and grouping are managed by the PivotTable, and refreshing it after source changes may be necessary. The AVERAGEIFS method makes the chosen lower and upper limits explicit in worksheet cells, which is easier to audit when bin boundaries are unusual or must be included in a report. Both approaches calculate grouped averages; choose based on whether flexibility or visible formula logic matters more.

Frequently Asked Questions

How do I average every five rows instead of every five X units?
That is a row-position grouping, not an X-value grouping. Add a group number based on row order, then average Y values by that group, or use a PivotTable after adding a group field. Sorting the data can change which observations fall into each five-row block.

Can I use a different starting point for the five-unit ranges?
Yes. Enter the first lower and upper limits you want, such as 2 and 7, then continue with 7 and 12. The formula uses the limits in the worksheet rather than assuming the bins begin at zero.

What happens when an interval has no observations?
AVERAGEIFS returns #DIV/0! when there are no qualifying numeric values to average. Check the bin edges and source values, and use a separate count if you need to label empty bins clearly.

Does AVERAGEIFS average the X values too?
No. It averages the range supplied as its first argument. In the example, that is the Y range in column B; column A is only used to decide which rows qualify. To average X within each interval instead, use the X range as the average range while keeping X as the criteria range.

Can bins contain decimals or negative X values?
Yes. AVERAGEIFS compares numeric limits and values, so limits such as -5, 0, and 5 work, as do decimal limits such as 2.5 and 7.5. The key is to use consistent units and non-overlapping boundaries.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.