Rolling Average with Blanks

[jblack6572]

See attached spreadsheet. In Column D, I am taking the average of the last 25 non blank values from Column C. The formula I'm using works, but as an array formula it takes an exceptionally long time to calculate (especially in the larger master spreadsheet of which it is a part). I'm looking for suggestions on a more efficient way to accomplish that, perhaps not using an array. Removing the blank rows is not an option, they are part of the overall data set and will need to remain. Any suggestions would be most appreciated.

[XOR LX]

Hi,

For a start, don't use IFERROR, which causes unnecessary calculations - explanation contained in my response here.

Secondly, avoid volatile functions like INDIRECT.

Try, in D11:

=IF(OR(A11="",COUNT($C$11:C11)<$B$4),"",SUM(IF(ROW($C$11:C11)>=LARGE(IF($C$11:C11<>"",ROW($C$11:C11)),$B$4),$C$11:C11))/$B$4)

Regards

[jblack6572]

Thanks, this works great. Incidentally, I tend to use a IFERROR a lot - wasn't aware that it was such an intensive function. Thank you for the tip - is there an easy alternative that you tend to defer to?

[XOR LX]

Thanks, this works great. Incidentally, I tend to use a IFERROR a lot - wasn't aware that it was such an intensive function. Thank you for the tip - is there an easy alternative that you tend to defer to?

Yes, as given in the link and also here, you can include a conditional statement prior to the main clause, such that the latter is not calculated unnecessarily for rows beyond the expected number of returns.

Regards