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! 


Vikas Dixit

2 Replies

 Calculate the average of a group of numbers

Calculate the average of numbers not in a contiguous row or column

To do this task, use the AVERAGE function. Copy the table below to a blank worksheet.


Description (Result)


Averages all of numbers in list above (9.5)


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)


Hello @Vikas51214,

I have made an example:



file is on the attached.