Sumproduct W/Offset to Create a Rolling Average for Filtered Cells

I'm trying to create a moving average of a W/L average that works with visible cells only. After a lot of research I finally found out how to create a function that works on visible cells only by using the sum product with offset function to give me a straight average.

=(SUMPRODUCT(SUBTOTAL(3,OFFSET(Table12[Net P/L],ROW(Table12[Net P/L])-ROW(I10),0,1)),--(Table12[Net P/L]>0))/SUBTOTAL(3,(Table12[Net P/L])) )

I now want to make that a moving average, but when I change the height in the offset reference I get either "#ref" or "#value", what do I need to change to get a moving average of a specific amount of cells. For this example let's say that # of cells is 10. So I want the average of the last 10 visible cells only. Is it as simple as just changing a number above? Or is it a completely different setup.


Also I'm new to Excel, I just started teaching myself the ins & outs a couple weeks ago to make my life easier, so please do not expect me to know certain things.

If more information is needed to provide me with the solution, please let me know.

Interesting issue. Ignoring hidden cells has some tricks (as you have found) but there may be a breaking point with what you can do.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Originally Posted by gtnewberry

.....So I want the average of the last 10 visible cells only...... Is it as simple as just changing a number above?

No, the OFFSET needs to remain the same so that you can evaluate each cell separately.

It would probably be a lot easier with a helper column (to indicate for each row whether it's visible or not).

Failing that you can use this generic formula - assuming Range is a single column of data

=AVERAGE(IF(SUBTOTAL(2,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),0,1)),IF(ROW(Range)>=LARGE(IF(SUBTOTAL(2,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),0,1)),ROW(Range)),10),Range)))