Calculating Averages Over a Time Range

**adm0104** · 06-10-2013, 10:57 AM

I have two tables - one where the first column is days and the second column is a list of pressure readings from those days. The second table's first column represents the beginning of a list of time ranges and the second column represents the end of those time ranges. I need to find the average of the pressure data over the range indicated by those columns. I have WAY too much data to do this manually - I need a function that pulls the start and end date from my spreadsheet and does all the calculation and referencing for me.

Below is an example spreadsheet.

The pressure data is on a table that looks like this.

Day	Pressure
1	8000
2	9000
3	8500
4	9500

And the second table looks like this. The AVERAGE() functions in the third column are what it would be if I just input what I wanted manually - the spreadsheet needs to figure out what range to average on its own. What would I want to input to get these averages?

Start Day	End Day	Average Pressure
1	3	AVERAGE(B2:B4)
1	2	AVERAGE(B2:B3)
2	4	AVERAGE(B3:B5)
3	4	AVERAGE(B4:B5)
1	4	AVERAGE(B2:B5)

Any help with this would be appreciated. I have tried using combinations of the AVERAGE() and IF() functions as well as AVERAGE() with INDEX() but have had no success yet.

Thanks in advance for the help!

**zumbalj** · 06-10-2013, 12:14 PM

You should be able to use the OFFSET function combined with AVERAGE. If you'd like more specific help setting the formula up, please upload a sample spreadsheet.

**adm0104** · 06-10-2013, 12:38 PM

Here is an example workbook. It's in Excel format right now - if you would prefer that I attach it as a jpeg or other image format just say so.

Example Workbook.xlsx

**zumbalj** · 06-10-2013, 12:53 PM

In F5 you can use

=AVERAGE(OFFSET($A$4,D5,1,E5-D5+1,1))

and drag down through all the yellow cells.

This formula will ONLY WORK IF your days are always in order like they are in your example, with no days skipped. If that might not be the case, we can probably modify the formula, but I'd rather not make it more complicated than it needs to be.

**FDibbins** · 06-10-2013, 12:59 PM

Or you could use the built-in averageifS() function...
=AVERAGEIFS($B$5:$B$19,$A$5:$A$19,">="&D5,$A$5:$A$19,"<="&E5)
This will work for any dates between those values