Advanced Lookup Formula Help

[zcheezy]

Hi all, I've used the site for help in the past, but this is my first post. I couldn't find a similar scenario and would appreciate any help here.

I am having trouble determining which formulas I need to use to get the values I want.

On my first sheet I have events and when they occurred by hour and date. The headers are date,event name,1,2,3,4,etc. Values are displayed if the event occurred during the intersecting date/hour.

On my second sheet I have the values I want to pull by date and hour. The headers are date(matches the values from sheet 1) and hour.

I want to put a formula in the last column on sheet 1 that will obtain the average of the values on sheet 2 for the date/hours the event occurred (i.e. only the hours that have a value in that row on sheet 1).

Ex:
Sheet 1:

[TABLE="width: 500"]
<tbody>[TR]
[TD]date[/TD]
[TD]event[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]Lookup formula to obtain average of values on sheet 2 for hours that event occured[/TD]
[/TR]
[TR]
[TD]8/19/2011[/TD]
[TD]xyz[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]55[/TD]
[TD]51[/TD]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](=average of value on sheet 2 for hours 9-12 on 8/19/2011)[/TD]
[/TR]
[TR]
[TD]8/19/2011[/TD]
[TD]abc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD]100[/TD]
[TD]102[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](=average of value on sheet 2 for hours 4-6 on 8/19/2011)[/TD]
[/TR]
[TR]
[TD]8/20/2011[/TD]
[TD]xyz[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD]46[/TD]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](=average of value on sheet 2 for hours 15-17 on 8/20/2011)[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2:
[TABLE="width: 500"]
<tbody>[TR]
[TD]date[/TD]
[TD]hour[/TD]
[TD]value[/TD]
[/TR]
[TR]
[TD]8/19/2011[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]8/19/2011[/TD]
[TD]2[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]8/19/2011[/TD]
[TD]3[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]
Continues for many more rows (hour1-24 for every day)

I have somewhat limited [FONT=inherit !important][FONT=inherit !important]Excel[/FONT][/FONT] experience. I don't know much about array formulas, so I wasn't sure if that was what I needed here. I would greatly appreciate any help.

 

[zcheezy]

If this proves to be too difficult, I also have the same data from sheet 1 in a different format that doesn't include a value for each hour. The headers are: date, start hour, end hour, event name. So in this case, we wouldn't need to reference an hour based on if the cell contains a number, we would simply need to average the values on that date and in between(inclusive) the start and end hours. I would prefer the first format because I want to use the values in my analysis.

Anyways...
Other formatted Sheet 1:
[TABLE="width: 500"]
<tbody>[TR]
[TD]date[/TD]
[TD]start[/TD]
[TD]end[/TD]
[TD]duration[/TD]
[TD]event[/TD]
[TD]formula to pull average of values in sheet 2 that occur on the date in column A between start and end time[/TD]
[/TR]
[TR]
[TD]8/19/2011[/TD]
[TD]15[/TD]
[TD]19[/TD]
[TD]5[/TD]
[TD]xyz[/TD]
[TD]=average value from sheet 2 between hour 15 and 19(inclusive) on 8/19/2011[/TD]
[/TR]
[TR]
[TD]8/19/2011[/TD]
[TD]5[/TD]
[TD]20[/TD]
[TD]16[/TD]
[TD]abc[/TD]
[TD]=average value from sheet 2 between hour 5 and 20(inclusive) on 8/19/2011[/TD]
[/TR]
</tbody>[/TABLE]

Thanks again!

 

[zcheezy]

I'm still working on a solution, so if anyone has any input it would be greatly appreciated!

 

[123rickfear]

Hi,

have you tried using the =averageifs function?

for example =AVERAGEIFS(Sheet2!C:C,Sheet2!A:A,Sheet1!A1,Sheet2!B:B," >= "&Sheet1!B:B,Sheet2!B:B," <= "&Sheet1!C:C)

I used the data from your second post as it seemed a bit clearer.

Rick

 

[zcheezy]

Worked great, thanks for the help!