Incremental Averages using VLOOKUP?

[gammyleg]

[TABLE="width: 560"]
<tbody>[TR]
[TD]Hi,

First post and struggling to add a snip even!

Currently using Excel 2007. I need to automate the process of calculating the incremental mean temperatures ( highlighted in red font ). The source data is from a thermometer that records hourly as shown in the right hand columns. If the mean temp was always calculated from hour 0, this would be a simple VLOOKUP exercise however because the time intervals in the source data continually changes ( in this instance are 0-2hr, 2-7hr, 7-12hr, 12-15hr etc ) I am stuck. Any help gratefully accepted.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 368"]
<tbody>[TR]
[TD="colspan: 2"]Report[/TD]
[TD][/TD]
[TD="colspan: 2"]Thermometer[/TD]
[/TR]
[TR]
[TD]Time Hrs[/TD]
[TD]Incremental Mean Temp[/TD]
[TD][/TD]
[TD]Hrs[/TD]
[TD]Temp[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]20.00[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]21.83[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: right"]28.17[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: right"]30.75[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD="align: right"]26.75[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]16[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]18[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

try

Excel 2010
	A	B	C	D
1	Report		Thermometer
2	Time Hrs	Incremental Mean Temp	Hrs	Temp
3
4	0		0	20
5	2	20.00	1	20
6	7	21.83	2	20
7	12	28.17	3	21
8	15	30.75	4	21
9	18	26.75	5	22
10			6	23
11			7	24
12			8	25
13			9	27
14			10	29
15			11	31
16			12	33
17			13	33
18			14	29
19			15	28
20			16	27
21			17	27
22			18	25
Sheet4

Cell Formulas
Range	Formula
B5	=AVERAGE(INDEX($D$4:$D$22,MATCH(A4,$C$4:$C$22,0)):INDEX($D$4:$D$22,MATCH(A5,$C$4:$C$22,0)))
B6	=AVERAGE(INDEX($D$4:$D$22,MATCH(A5,$C$4:$C$22,0)):INDEX($D$4:$D$22,MATCH(A6,$C$4:$C$22,0)))
B7	=AVERAGE(INDEX($D$4:$D$22,MATCH(A6,$C$4:$C$22,0)):INDEX($D$4:$D$22,MATCH(A7,$C$4:$C$22,0)))
B8	=AVERAGE(INDEX($D$4:$D$22,MATCH(A7,$C$4:$C$22,0)):INDEX($D$4:$D$22,MATCH(A8,$C$4:$C$22,0)))
B9	=AVERAGE(INDEX($D$4:$D$22,MATCH(A8,$C$4:$C$22,0)):INDEX($D$4:$D$22,MATCH(A9,$C$4:$C$22,0)))

 

[gammyleg]

Scott,

Thanks for the speedy response. I'll try this out and confirm it works.

 

[gammyleg]

Scott,

Just run through your fix and it works perfectly. Thanks again for your help.

Has unfortunately opened up another worm-can regarding how my Excel spreadsheet queries data from my Access database.

 

[Scott T]

You are welcome.

I do not use Access much but post your problem and maybe someone can help.

 

[gammyleg]

Scott,

More of an Excel problem I think.

After successfully inputting your suggested formulae I then attempted to increase the range of results that this workbook imports from my Access database. I have three separate query tables running which have to be refreshed individually. I managed to extend the query ranges on two of them but the third refuses to play ball and displays the following message:

'This operation is not allowed. The operation is attempting to shift cells in a table in your worksheet'

No clue what this means and have spent most of the morning trying to work it out. I know that I can start the queries again from scratch but that would then mean retyping all the links and calcs on subsequent worksheets that rely on the data that is imported.