Assuming that A2:B7 contains the data, and G2:G10 contains a list of holidays, let D2 contain 5 (indicating a 5 day query), then try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=AVERAGE(IF(ROW($A$2:$A$7)<=SMALL(IF(ISNA(MATCH($A$2:$A$7,$G$2:$G$10,0)),ROW($A$2:$A$7)),D2),IF(ISNA(MATCH($A$2:$A$7,$G$2:$G$10,0)),$B$2:$B$7)))

Adjust the ranges accordingly, and amend the list of holidays and its reference, as needed. Replace AVERAGE with MAX, MIN, and STDEV...

Hope this helps!

## Like this thread? Share it with others