Average of data excluding gaps

Hey everyone,

I have a spreadsheet containing both valid and invalid data and have created a column indentifying the valid data using If statements. I want to make a column containing the average of the previous 720 valid data points. For example, column A is data, column B is the valid flag, and column C will be my average. Let's say cells A1 through A720 are valid, cells A721 through A741 are invalid, and the following data is all valid. Cell C720 should return "average(A1:A720)" and cells C721 through C741 should be blank. For the following cells, cell C742 should return "average(A2:A720,A742)", cell C743 should return "average(A3:A720,A742:A743)". It's difficult to explain, I've attached a sample spreadsheet with an example of the desired results but using an average of 10 valid points so it's easier to work with.

I'm thinking there may be a way to do this using an if statement, but I have no idea. Any help is appreciated.

Thank you, but perhaps I should have tried to come up with a more descriptive title. I don't want to delete the invalid data, so the cells won't actually be blank. They're just gaps in what data is valid. I need each average to be that of 720 valid data cells, excluding the invalid ones. So, if there's a chunk of invalid data the averages should include all data back to the gap, skip the gap, and make up the difference using data before the gap.

I see what you mean, I have a similar column which displays blanks for invalid data in my full spreadsheet. However, the average is a 720 hour rolling average for a sheet containing thousands of lines of data. Each average must contain exactly 720 of the previous valid data points.

Yes, sort of. It's a rolling average, so every hour you average the previous n hours of valid data, where n is the averaging time, in this case 720. What you described is a block average, where every 720 valid hours you have one average.

The desired results are the "Average" column in my original sheet, sorry I should have clarified that. The pivot table may works in theory, but it isn't practical for what I need to do with the data.

Oh that's getting close to what I need! Thank you, I didn't know about averageif. Now I need it to find exactly ten cells (or 720 for my full sheet) to average. I may be able to work with this and use more indicator/flag columns

I'm beginning to think the only way to do exactly what I want is to use a macro, but I was hoping to avoid that haha