Array formulas for a large dataset

**Freedan** · 05-25-2012, 08:46 AM

Hi all

Apologies in advance if this is the wrong section for this. At the moment, I'm trying to create a macro that involves an array formula that will allow me to search for hourly data under a certain criteria and calculate the average data for each hour of the day. Would anyone be able to say how this is possible? I've been thinking of using a lookup or vlookup function except doing so returns exact values for the criteria. The problem with that is the criteria (lookup value) doesn't match with the table array and I'm not allowed to change either one. Also I've been wondering how to create such an array function without selecting the hourly data as each month, the data changes and may not be in the same cell ranges. Thanks in advance!

**Pulsus** · 05-25-2012, 08:49 AM

Freedan,

Can you please attach a Sample File. That will make easier to help you.

**Freedan** · 05-25-2012, 10:11 AM

Thank you Pulsus

I edited my post with an attached sample. What I'm essentially hoping to do is create an array formula that calculates the hourly averages and places the results in sheet 2 where the averages are collected and automate the process of calculating the averages for each product (Relayserve Base and Max). I'm trying to do this by creating an array formula recorded in a macro that will allow me to fill out the averages in sheet to without having to do it manually. Since the cell ranges change each month I extract raw data from a database, I'm trying to do this without having to select the hourly data in sheet 1 for the formula. Even knowing if such a thing is impossible would help me quite a bit. Thank you ^.^

**Pulsus** · 05-25-2012, 12:48 PM

Freedan,

I think this is what are you looking for. I didn´t use a macro, but a function -- DAVERAGE -- that calculate the average of a criteria.

I make this for 10.000 lines, is that enought?

Hope this can help, here is the file:

Sample(1).xlsx