Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Formula to extract info from every n row


Hi, this may be a simple question, but I have long columns of hourly time-stamped data from which I do averages, such as 24 1-hour values going into a 24-hour average.  I may have hundreds to thousands of such averages, so I'm looking for a formula-based way (as opposed to filtering out the blanks in the avg. column) to put all those averages into one smaller list.

Please recommend a good way to do this.

Thanks in advance.


EDIT:  Additional description

OK. Thanks for the responses so far--this is the first time using this forum, and I've been way too vague. Attached is an example file:  Contains hourly time stamps in col a, with data in col b, and then the 24-hour avg in col c, whick references the 24 hours starting with midnight.  The blanks that I am referring to are the blanks under the average.  What I have done in the past is do use this method and then filter out the blanks in col c, then copy the result to another table.  Kind of cumbersome, plus if something changes in the data, that whole process needs to be repeated.  I'm trying to get more efficient.

I haven't tried the one suggestion yet, but will do so soon.



I'm put off by your mentioning "blanks in the avg column". This is problematic on two counts. The first is that you don't seem to have 24 rows with 24 hourly values in them from which to draw a daily average. You may have 24 rows for 24 hours with some values missing, represented by blanks. Or you may have 24 hourly readings in more than 24 rows with some rows having no values in them. Either way it would be hard to determine the cell range from which data for averaging is to be obtained. I suggest you post a sheet with sample data.
The second issue seems to be one of naming. The task is to draw daily averages which would result in an "avg column" somewhere (you don't say where but the presumption would be to have it on a separate sheet). The hourly data should be in the "data column" even if they are, in fact, hourly averages. I am confused by your claim that there are "blanks in the avg column". How could there be, unless you refer to the data as averages? If so, what do you wish to call the compounded averages?
Variatus (rep: 4889) May 22, '19 at 10:08 pm
Did you try my formula? It does exactly what you're asking. I added an update to work specifically with your data set. 
don (rep: 1989) May 24, '19 at 12:57 pm
Add to Discussion


Selected Answer

Here is probably one of the easiest formulas I've used to do this:


$A$1 is the cell where the data to average begins.

$D$1 is the cell where the first average formula is entered, or just in the same row, it doesn't matter.

3 is the number of rows between an average. Change both to the desired number.


This is an updated version of the above formula that should work for your data set.


Let me know if this is what you were looking for :)



Thanks so much, this works exactly how I need it to and will save me tons of time in the future.
EricW (rep: 2) May 24, '19 at 7:41 pm
Also, it occurred to me that I need the date from col a to be put in front of the average that results from your original formula.  I tried removing the average part and addressed it to col a, but it produced an error. How would that next step be done?  Thanks
EricW (rep: 2) May 24, '19 at 7:48 pm
use concatenation, so =A1&ORIGINAL Formula. If that doesn't help, then just go ahead and ask a separate new question for this in the forum.
don (rep: 1989) May 28, '19 at 12:03 am
Add to Discussion

The formula below can replace the formula in C2 of your worksheet from where it can be copied down. It will produce a fatal error if copied into the first row.

=IF(INT($A2)=INT(SUM($A1)),"", AVERAGEIFS($B:$B,$A:$A,">="&INT($A2),$A:$A,"<"&INT($A2)+1))

The IF statement uses INT($A2) to extract the day from the date in column A. INT(SUM($A1)) returns zero if A1 doesn't contain a date or other number when the plain INT($A1) would return an error. The effect is that an average is drawn whenever the date changes. Therefore it doesn't matter how many entries there are per day.

The AVERAGEIFS function averages values between the date in column A (extracted from the date/time value there) and the next day, where the exact date value is included (>=) and the next one excluded (<).

My formula specifies all columns as absolute which prevents it from being copied horizontally. The private rule I implement is that all addresses should be absolute unless copying, horizontally or vertically, is intended. In the case of this fomrula vertical copying is intended. Therefore row numbers are relative in my formula, indicating that it supports vertical copying.



This formula provides the averages, but does not put them all at the top in a separate list like I'm hoping to do. Am I doing something wrong?
EricW (rep: 2) May 24, '19 at 7:47 pm
I don't know if you did anything wrong. It seems to me that my formula does exactly what you previously did manually as explained in your revised question. There was no mention of having a separate list and no design of what you want the separate list to look like, like having a column for the date. IMHO a short list can be created by simply filtering out the blanks in the average column. Anyway, you have selected another answer. By this forum's rules that limits the thread. I shall not expand on my answer unless such a request is made within the context of a new question.
Variatus (rep: 4889) May 24, '19 at 11:35 pm
OK, thanks. I will submit another question--as I said, I'm new to this forum and its rules.
EricW (rep: 2) May 25, '19 at 12:33 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login