Calculate Average days

I want a formula to calculate the average days (of a given set of days) and EXCLUDE any day above 100.

I have more info on the attached sheet.

which version do you need this to run in ?

For XL2007:

Q5:
=IFERROR(AVERAGEIF(INDEX($A$3:$O$10,0,3*ROWS(Q$5:Q5)),"<=100"),"")
copied down

For versions prior to that you can use SUMIF/COUNTIF approach (or an Array if you prefer)

I think you misinterpreted the prior post.

AVERAGEIF is a function exclusive to XL2007 and beyond.

For versions prior to Excel 2007 your options for the same are either a SUM/COUNT approach else an Array.

2003 version below:

Please Login or Register  to view this content.