Average if month and hour match

**pichon** · 04-14-2011, 01:13 AM

I am using historical solar data to try to figure out typical solar radiation levels for any given month at any given hour.

In column A I have a list of dates with hours in "m/d/yyyy h:mm" format. In column B I have the solar radiation level recorded on that date at that time.

Now I want to create a range of cells with 24 rows (hours) and 12 columns (months). In each cell I want to have an average of the recorded solar radiation levels for each month and each hour.

I tried averageifs but I don't fully understand how to use it.

I know I could use a pivot table to accomplish this, however I want to use a formula so that I can convert it to R1C1 and paste it in using VBA.

Ive attached what I am working with.

**bentleybob** · 04-14-2011, 02:29 AM

The attached uses two helper columns to extract the month and hour from your date/time, which makes AVERAGEIFS easier to use. Maybe this works for you?

**pichon** · 04-14-2011, 02:45 AM

That works beautifully. Thank you very much!

**devansh** · 05-09-2016, 08:58 AM

thank you @bentleybobb. However I couldn't manage to match the month while the month is displayed in text format. I had to enter numbers manually. Why is that?

**FDibbins** · 05-09-2016, 12:22 PM

devansh welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.