Average of Days from Date Range

I currently have the following formula to calculate average of days within a specific date range in excel. The question I have is will this formula give the correct average? Right now, it pulls data from a different sheet from column C and D within the date range defined on the worksheet where the average will be displayed. Column G specifies the days in the same worksheet as the dates. I gives me an average but, is this formula correct in giving an accurate average of days? Or will I need to only specify the date range in col C because the days are defined in col G for the average. Any help is appreciated, thanks.

=AVERAGE(IF('SHEET NAME'!$C:$D>=A16,IF('SHEET NAME'!$C:$D<=C16,'SHEET NAME'!$G:$G)))

Where A16 is the startdate and C16 is the enddate specified on the worksheet where the average will be.

Originally Posted by NBVC

If there is a match in both C and D for either condition, the value in G will be included twice for each match...

try instead:

=AVERAGE(IF(('SHEET NAME'!$C:$C>=A16)+('SHEET NAME'!$D:$D>=A16),IF(('SHEET NAME'!$C:$C<=C16)+('SHEET NAME'!$D:$D<=C16),'SHEET NAME'!$G:$G)))

it is also recommended that you don't use whole column references with array formulas due to lack of efficiency.

Thank you very much it worked!! Also thanks for the heads up, I will try and define the range instead of the whole column.