How to Do Cumulative Averages in Excel

Save

Depending on your spreadsheet's format, this might use cumulative averages one of two ways. If you're simply displaying the cumulative average in a single cell, you need to include current and future values, which won’t happen if new data falls outside of specified cell ranges. If you're calculating the cumulative average for each record you enter, you have to be careful of relative references that might alter your formula when you copy it to the next record. Excel 2013 supports absolute references to avoid this problem.

Single Results

  • Locate the data column or row that contains the data you wish to average. As an example, you might have data in column C.

  • Type "=Average(range)" without quotes (here and throughout) in a cell located in another column.

  • Change "Range" to reference the column using the format "letter:letter." In the example, you would use "=Average(C:C)" to reference the entire column. To reference rows, use the row numbers, such as "=Average(2:2)" to average the second row. This format only averages numbers, so the column or row can contain text headers without creating errors. Empty cells are not included in the average, but cells with a zero are.

Recurring Averages

  • Click a cell that corresponds to the first data value in an empty column. As an example, if the column data you want averaged starts at cell C2, you might click cell D2.

  • Enter "=Average(range)" in the cell, and change "range" to the actual range of data. Because the example is starting with the first data value, you'd enter "=Average(C2:C2)" for now.

  • Add dollar signs to the initial cell reference so it does not change when you copy the formula. In the example, the formula changes to "=Average($C$2:C2)" to keep the beginning reference static while allowing the ending reference to change. As an example, if you copied the formula to cell D10, the formula automatically changes to "=Average($C$2:C10)." This procedure also works for rows.

References

  • Photo Credit Goodshoot/Goodshoot/Getty Images
Promoted By Zergnet

Comments

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!