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:

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

<tbody>
</tbody>

Sheet 2:

date	hour	value
8/19/2011	1	5
8/19/2011	2	6
8/19/2011	3	7

<tbody>
</tbody>

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:

date	start	end	duration	event	formula to pull average of values in sheet 2 that occur on the date in column A between start and end time
8/19/2011	15	19	5	xyz	=average value from sheet 2 between hour 15 and 19(inclusive) on 8/19/2011
8/19/2011	5	20	16	abc	=average value from sheet 2 between hour 5 and 20(inclusive) on 8/19/2011

<tbody>
</tbody>

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!