Forum Discussion
How to calculate averages of different, non-overlapping cell ranges in Excel using a single formula
Hello dear experts! This is Vikas Dixit from India. I wish to calculate averages of different non-overlapping cell ranges in Excel using a single formula, is it possible anyway? To elaborate it further, suppose, I have 4000 observations arranged in a column, say from A1 to A4000. Now I wish to compute averages for each range of 30 observations, i.e., from A1 to A30; from A31 to A60; from A61 to A90, and so on. For this, I want a single formula, which if I copy down to next cell works well. Is there a way out? Please note that being visually challenged, I depend on screen reader, and hence, I use only keyboard (and not mouse), and I have tried to find the answer of this problem on Google, but no satisfactory answer. Please provide me with any possible way out! Thank you in advance!
Regards!
Vikas Dixit
2 Replies
Calculate the average of a group of numbers
Calculate the average of numbers not in a contiguous row or columnTo do this task, use the AVERAGE function. Copy the table below to a blank worksheet.
Formula
Description (Result)
=AVERAGE(A2:A7)
Averages all of numbers in list above (9.5)
=AVERAGE(A2:A4,A7)
Averages the top three and the last number in the list (7.5)
=AVERAGEIF(A2:A7, "<>0")
Averages the numbers in the list except those that contain zero, such as cell A6 (11.4)
