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.

CUST ID #	NAME	SALES	INVOICE DUE DATE	AMOUNT	REASON	DATE COMPLETE
1234	John Doe	Dr. Dre	7/1/2017	$3.50	Other	7/18/2017
1234	Jane Doe	Dr. Dre	7/1/2017	$3.50	Mismatch

<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>
</tbody>

 

[missilepilot]

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

CUSTOMER ID #	NAME	SALES EXECUTIVE	7/10/2017	6/10/2017	5/10/2017	04/10/2017 and older	AMOUNT		DATE COMPLETE	PAST DUE RANGE
1238	John Doe	Mr. A	0	119.86	0	0	$119.86			39
35144	Jane Doe	Mr. A	6407.42	0	0	0	$6,407.42			9

<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>
</tbody>

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!