In column A I have a list of all days for the year of 2014, formatted as "Tuesday, October 21, 2014". Then I have a table of the months in 2014, formatted at "October". How can I pull the average times for each of those months based on the two formats above?
I assume it is some sort of nested IF statement with a VLOOKUP, but I cannot get it to work? Any ideas?



Here is what my table looks like:

A2=October B2=the average time
The date ranges begin in column A starting at A8 and going down. The times completed are in column B starting at B8 and going down.

Thank you!

You can do it easily with a pivot. I did one in about 2 minutes using your example. Do you know how to do that? I would attach my own but for some reason the attachment manager isn't working for me.

The key is to group the dates by Month/Year and then set up the Time value as average.

hi there!
My compliance manager does not want a table. Here is my sheet. do you know of a formula, besides a pivot table?

B2, B3, B4 - I wish to populate with the average times in each month the pricing was complete.

Attached Files

Book1.xlsx (12.5 KB, 9 views)

Yo!

There's managers for ya. Always making an easy job a huge PITA.

So, yeah, there's a way to do it...provided, of course, your manager doesn't object further.

First off, you need to make the months in A2:A4 date values that Excel can recognize. So rather than October, November, December enter 10/1/14, 11/1/14, 12/1/14. Now use a customer format ("mmmm") to have it display as you have it in your sheet.

Then you can get the average for October with the following equation:

=IFERROR(SUMPRODUCT((A$8:A$182>=A2)*(A$8:A$182<DAT E(YEAR(A2),MONTH(A2)+1,1))*(B$8:B$182))/SUMPRODUCT((A$8:A$182>=A2)*(A$8:A$182<DATE(YEAR(A2 ),MONTH(A2)+1,1))*(B$8:B$182>0)),"")

The IFERROR suppresses an error that the formula will return if there is no data for the month (thus resulting in a divide by zero).

The first SUMPRODUCT sums the times for dates on or after 10/1/14 (the value in A2) and less than 11/1/14 (the value in A3).

The second SUMPRODUCT counts these same times and thus the average is gotten by dividing the first by the second.

This formula can then be copied down for November and December.

Totally!! thank you for your help. one more. I entered in the formula, but it is just a blank cell now?? I tried reformatting it but nothing...any ideas for this one?

Attached Files

Book1.xlsx (12.7 KB, 9 views)

I don't know how, but it looks like a parenthesis was moved.

Your sheet's formula starts as:
=IFERROR((SUMPRODUCT(A$8:A$182>=A2)...

That second parenthesis before SUMPRODUCT should be after it:
=IFERROR(SUMPRODUCT((A$8:A$182>=A2)...

Just copy the equation from the post a few above again and paste it again. There is an extraneous space that this forum put in one of the DATE's. I tried to get rid of it, but it keeps appearing.

Probably just better to move the parenthesis.

that worked!!

thank you thank you!