Average of difference between two dates, across multiple sheets

[missilepilot]

So I need to compute the average number of days between two columns, with multiple instances of each start and end date, across multiple sheets, and only including cells that occur in a row with a matching value.

=AVERAGE(DAYS(IF(MATCH(B4,INDIRECT("'" &SHEETLIST& "'!$B3:$B300")),INDIRECT("'" &SHEETLIST& "'!$H3:$H300")),IF(MATCH(B4,INDIRECT("'" &SHEETLIST& "'!$B3:$B300")),INDIRECT("'" &SHEETLIST& "'!$E3:$E300"))))

Is where I'm at so far. SHEETLIST is the list of all sheets, looking for a match in B3:B300 of those sheets, and then comparing the distance between dates listed in H3:H300 and E3:E300 of those sheets, computing the average number of days utilizing those ranges, and returning it to a summary sheet. Column H will not always contain a date, in that case I want it to use today's date for determining time elapsed. I realize i'm getting close to the boundaries of what formulas are capable of, but my knowledge of VBS is limited. I am studying VBS, but i need to complete this workbook in a timely fashion. Any help would be greatly appreciated.

[TABLE="width: 821"]
<colgroup><col style="width:59pt" width="79"> <col style="width:128pt" width="171"> <col style="width:95pt" width="126"> <col style="width:93pt" width="124"> <col style="width:56pt" width="75"> <col style="width:113pt" width="150"> <col style="width:72pt" width="96"> </colgroup><tbody>[TR]
[TD="class: xl95, width: 79"]CUST ID #
[/TD]
[TD="class: xl96, width: 171"]NAME
[/TD]
[TD="class: xl96, width: 126"]SALES
[/TD]
[TD="class: xl19, width: 124"]INVOICE DUE DATE[/TD]
[TD="class: xl98, width: 75"]AMOUNT[/TD]
[TD="class: xl23, width: 150"]REASON
[/TD]
[TD="class: xl19, width: 96"]DATE COMPLETE
[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 79"]1234
[/TD]
[TD="class: xl22"]John Doe
[/TD]
[TD="class: xl22"]Dr. Dre
[/TD]
[TD="class: xl23, width: 124"]7/1/2017
[/TD]
[TD="class: xl23, width: 75"]$3.50
[/TD]
[TD="class: xl23, width: 150"]Other[/TD]
[TD="class: xl22"]7/18/2017
[/TD]
[/TR]
[TR]
[TD="class: xl34, width: 79"]1234
[/TD]
[TD="class: xl22"]Jane Doe
[/TD]
[TD="class: xl22"]Dr. Dre
[/TD]
[TD="class: xl23, width: 124"]7/1/2017
[/TD]
[TD="class: xl23, width: 75"]$3.50[/TD]
[TD]Mismatch
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl34, width: 79"][/TD]
[TD="class: xl33, width: 171"][/TD]
[TD="class: xl33, width: 126"][/TD]
[TD="class: xl33, width: 124"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl34, width: 79"][/TD]
[TD="class: xl33, width: 171"][/TD]
[TD="class: xl33, width: 126"][/TD]
[TD="class: xl33, width: 124"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl34, width: 79"][/TD]
[TD="class: xl33, width: 171"][/TD]
[TD="class: xl33, width: 126"][/TD]
[TD="class: xl33, width: 124"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl34, width: 79"][/TD]
[TD="class: xl33, width: 171"][/TD]
[TD="class: xl33, width: 126"][/TD]
[TD="class: xl33, width: 124"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl34, width: 79"][/TD]
[TD="class: xl33, width: 171"][/TD]
[TD="class: xl33, width: 126"][/TD]
[TD="class: xl33, width: 124"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl34, width: 79"][/TD]
[TD="class: xl33, width: 171"][/TD]
[TD="class: xl33, width: 126"][/TD]
[TD="class: xl33, width: 124"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl34, width: 79"][/TD]
[TD="class: xl33, width: 171"][/TD]
[TD="class: xl33, width: 126"][/TD]
[TD="class: xl33, width: 124"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[missilepilot]

So i worked out this issue by having each sheet compute date ranges individually.

[TABLE="width: 1609"]
<colgroup><col style="width:59pt" width="79"> <col style="width:128pt" width="171"> <col style="width:95pt" width="126"> <col style="width:68pt" width="90"> <col style="width:69pt" width="92"> <col style="width:65pt" width="86"> <col style="width:102pt" width="136"> <col style="width:56pt" width="75"> <col style="width:99pt" width="132"> <col style="width:72pt" width="96"> <col style="width:104pt" width="139" span="2"> <col style="width:106pt" width="141"> <col style="width:80pt" width="107"> </colgroup><tbody>[TR]
[TD="class: xl45, width: 79"]CUSTOMER ID #[/TD]
[TD="class: xl46, width: 171"]NAME
[/TD]
[TD="class: xl46, width: 126"]SALES EXECUTIVE[/TD]
[TD="class: xl159, width: 90, align: right"]7/10/2017[/TD]
[TD="class: xl159, width: 92, align: right"]6/10/2017[/TD]
[TD="class: xl159, width: 86, align: right"]5/10/2017[/TD]
[TD="class: xl159, width: 136"]04/10/2017 and older[/TD]
[TD="class: xl45, width: 75"]AMOUNT[/TD]
[TD="class: xl48, width: 132"][/TD]
[TD="class: xl48, width: 96"]DATE COMPLETE
[/TD]
[TD="class: xl48, width: 107"]PAST DUE RANGE[/TD]
[/TR]
[TR]
[TD="class: xl155, width: 79, align: right"]1238
[/TD]
[TD="class: xl155, width: 171"]John Doe
[/TD]
[TD="class: xl155, width: 126"]Mr. A
[/TD]
[TD="class: xl155, width: 90, align: right"]0[/TD]
[TD="class: xl155, width: 92, align: right"]119.86[/TD]
[TD="class: xl155, width: 86, align: right"]0[/TD]
[TD="class: xl155, width: 136, align: right"]0[/TD]
[TD="class: xl56, width: 75, align: right"]$119.86[/TD]
[TD="class: xl56, width: 132"][/TD]
[TD="class: xl54"][/TD]
[TD="class: xl19, align: right"]39[/TD]
[/TR]
[TR]
[TD="class: xl155, width: 79, align: right"]35144
[/TD]
[TD="class: xl155, width: 171"]Jane Doe
[/TD]
[TD="class: xl155, width: 126"]Mr. A
[/TD]
[TD="class: xl155, width: 90, align: right"]6407.42[/TD]
[TD="class: xl155, width: 92, align: right"]0[/TD]
[TD="class: xl155, width: 86, align: right"]0[/TD]
[TD="class: xl155, width: 136, align: right"]0[/TD]
[TD="class: xl56, width: 75, align: right"]$6,407.42[/TD]
[TD="class: xl61"][/TD]
[TD="class: xl61"][/TD]
[TD="class: xl19, align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]

Now i need to have it compute using only the oldest date of entry. For example, John Doe should compute using the 6/10 value, while Jane Doe computes using the 7/10 value. The issue I'm having is trying to get the formula to only use the oldest if more than one entry. So if John Doe had a value in 7/10 column and 6/10 column, if would use the 6/10 for computation. I've tried two variations:

=DAYS(IF($K4>0, $K4, $O$2),IF($E4>0,$E$3,IF($F$4>0,$F$3,IF($G$4>0,$G$3,IF($H$4>0, $H$3,""))))) Which works great with a single value, but doesn't account for two different dates and force the oldest date.

So then I tried:

=MAX(DAYS(IF($K9>0,$K9,$O$2),OR(IF($E9>0,$E$3,""),IF($F$9>0,$F$3,""),IF($G$9>0,$G$3,""),IF($H$9>0,$H$3,"")))) in hopes of using the largest value, which would be the oldest date range. But i can't quite get it to return anything other than #Value.

Once again, any help would be greatly appreciated. Thanks!