Average Time Spent by Category Between Two Dates

[malonex3]

Hello Again,

I wanted to say thank you for the help on my first post, Count Values that Fall Between Two Dates.

Now I need to expand on this. In the summary report I need to average the time it takes to complete jobs by category.

For example, if there were 100 "computer" jobs completed during a given time period what was the average time it took to complete those jobs?

Sheet 1: Column A contains the dates, column E contains the categories, and column P calculates the time it took to complete a job.

Sheet 2: Cell C13 needs to calculate the average time it took to complete the total number of "computer" jobs that occurred between the start and end dates. The start and end dates are in C2 and E2 respectively.

I appreciate and assistance.

 

[GlennUK]

What version of Excel?

 

[malonex3]

Sorry, Excel 2007.

 

[GlennUK]

Have you tried to do what you want with the AVERAGEIFS function?

 

[malonex3]

Hey Glenn,

Yes I have tried the AVERAGEIFS function, but without any luck. The problem seems to be in restricting the data to the dates entered in cells C2 and E2. If I do an AVERAGEIFS and use the Time to Repair range as the range to average and use the dates, and category as the criteria I end up with the dreaded #DIV/0! error. If I use a simple AVERAGEIF and just use the category as the only restriction it works fine. The problem with this is that the first sheet will contain several months worth of data. I need to be able to restrict to just a given period of time, typically one month but that could vary so I need this to be as flexible as possible.

I do not have access to the file right now or I would post an example of what I am trying to accomplish. I will try tomorrow.

 

[T. Valko]

Hey Glenn,

Yes I have tried the AVERAGEIFS function, but without any luck. The problem seems to be in restricting the data to the dates entered in cells C2 and E2. If I do an AVERAGEIFS and use the Time to Repair range as the range to average and use the dates, and category as the criteria I end up with the dreaded #DIV/0! error. If I use a simple AVERAGEIF and just use the category as the only restriction it works fine. The problem with this is that the first sheet will contain several months worth of data. I need to be able to restrict to just a given period of time, typically one month but that could vary so I need this to be as flexible as possible.

I do not have access to the file right now or I would post an example of what I am trying to accomplish. I will try tomorrow.

Try this...

Use cells to hold the criteria:

• Sheet2 C2 = lower date boundary
• Sheet2 E2 = upper date boundary
• Sheet2 F2 = some category like Computer

Then:

=AVERAGEIFS(Sheet1!P2:P100,Sheet1!A2:A100,">="&C2,Sheet1!A2:A100,"<="&E2,Sheet1!E2:E100,F2)

 

[malonex3]

=AVERAGEIFS(Sheet1!P2:P100,Sheet1!A2:A100,">="&C2,Sheet1!A2:A100,"<="&E2,Sheet1!E2:E100,F2)

I did a quick test here at home and it appears T. Valko's formula is going to work. I'll know for sure when I can use it on live data. Thanks!

Just so I understand the formula though. Why does putting the & between >=, and <= operators make this formula work? I created a formula very similar to this, but without the ampersands that kept resulting in the #DIV/0! error.

Again, thank you. This will make my reporting much more efficient, and flexible.

 

[malonex3]

=AVERAGEIFS(Sheet1!P2:P100,Sheet1!A2:A100,">="&C2,Sheet1!A2:A100,"<="&E2,Sheet1!E2:E100,F2)

I tried a quick test here at home and it appears this is going to work. I'll know for sure when I can try it out with live data. Thanks!

Just so I understand the formula though; why does using a & after the ">=" and "<=" operators make this formula work? I wrote a formula very similar to this one but without the ampersands that resulted in the #DIV/0! error.

Again, thank you. This will make my reporting much more efficient and flexible.

 

[T. Valko]

I tried a quick test here at home and it appears this is going to work. I'll know for sure when I can try it out with live data. Thanks!

Just so I understand the formula though; why does using a & after the ">=" and "<=" operators make this formula work? I wrote a formula very similar to this one but without the ampersands that resulted in the #DIV/0! error.

Again, thank you. This will make my reporting much more efficient and flexible.

I don't know the technical reason for having to do that. I just know from trial and error that's what we have to do in some functions including AVERAGEIFS.

You have to quote and concatenate the operators AND other functions.

">="&A1
">="&TIME(1,0,0)