Averageif with Date range help

[hofst038]

Hey guys, got a tough one that I need help with:

Date Devices Installed Lead Time on Site (Hours) Non Lead Time on Site(Hours) 3/11/2016 2 2.00 3.00 3/15/2016 5 4.00 6.00 3/17/2016 1 1.00 4/1/2016 2 1.25 0.75 <tbody> </tbody>

<tbody>
</tbody>

I am trying to find the average between the lead time on site (column C) and the non lead time on site (Column D) as it pertains to a date range. For the date range of 3/11-3/18 I need the average time it took the techs to complete the install. In some cases there was not a second technician so it would be just the average time of the lead tech on that date
- The answer/average for the date range of 3/11-3/18 I am looking for is 3.2

My formula (below) is spitting out a different number and I cant get it right. Any suggestions are helpful!

=AVERAGEIFS($C$2:$C$5,$A$2:$5,">="&”3/11/2016”,$A$2:$A$5,"<="&”3/18/2016”)+AVERAGEIFS($D$2:$D$5,$A$2:$A$5,">="&”3/11/2016”,$A$2:$A$5,"<="&”3/18/2016”)/AVG(IF(AND(ISNUMBER(C3),ISNUMBER(D3)),AVERAGE(C3:D3/2),AVERAGE(C3:D3)))

 

[Jonmo1]

Not sure I follow how you're wanting to do the actual Math.

How did you come to 3.2 as your desired result ? Which numbers exactly is 3.2 the average OF, and Why ?

 

[hofst038]

I probably did not do a great job of making sense. 5 total technicians within the date range of 3/11/2016-3/18/2016. Total hours for the technicians in the date range of 3/11/2016-3/18/2016 = 16. Total hours/technicians (16/5=3.2).

 

[Jonmo1]

OK, I get it.

Now, can we assume the dates are sorted in AScending order?
And are the 2 technician columns contiguous, like Column D and Column E ?

 

[hofst038]

Dates are from oldest to newest and yes the technicians columns are contiguous.

 

[Jonmo1]

Disregard those questions, I was thinking there might be an easier way.

This seems to work

=SUMPRODUCT(($A$2:$A$5>=$H$2)*($A$2:$A$5<=$I$2)*$C$2:$D$5)/SUMPRODUCT(($A$2:$A$5>=$H$2)*($A$2:$A$5<=$I$2)*($C$2:$D$5<>""))

I put the start and end dates in H2 and I2

 

[hofst038]

Nailed it, thanks! Taking it a step further....I now want to divide the average install time by number of devices on that corresponds to that date, would my formula look like this:

=SUMPRODUCT(($A$2:$A$5>=$H$2)*($A$2:$A$5<=$I$2)*$C$2:$D$5)/SUMPRODUCT(($A$2:$A$5>=$H$2)*($A$2:$A$5<=$I$2)*($C$2:$D$5<>""))/SUMPRODUCT(($A$2:$A$5>=$H$2)*($A$2:$A$5<=$I$2)*$B$2:$B$5)

 

[Jonmo1]

I think you need to add parens around the the first average..

=(SUMPRODUCT(($A$2:$A$5>=$H$2)*($A$2:$A$5<=$I$2)*$C$2:$D$5)/SUMPRODUCT(($A$2:$A$5>=$H$2)*($A$2:$A$5<=$I$2)*($C$2:$D$5<>"")))/SUMPRODUCT(($A$2:$A$5>=$H$2)*($A$2:$A$5<=$I$2)*$B$2:$B$5)

 

[hofst038]

Perfect, thanks again!

 

[Jonmo1]

You're welcome.