iCloud Numbers formula over filtered rows ex.SUBTOTAL(101;A1:A10)

I use Swedish version of Numbers therefore the translation of syntax can be wrong, apologise for that.

I have a table where I import stock data from the market. Closed market days got a "-" in the cell and these

are filtered to hide in the table.

In a column where an average of three earlier days is calculated with the formula SUBTOTAL(101;A1:A3). Formula is used in all cells in the column. It works well for Monday to Wednesday.

But on Thursday it will calculate Thursday, Friday and Saturday instead of Thursday, Friday and Monday.

And on Friday it calculate Friday, Saturday and Sunday instead of Friday, Monday and Tuesday.

How can I use the SUBTOTAL formula to jump over filtered rows?

I hope I have explain well.

Thank you in advance!

MacBook Pro 13″, macOS 10.15

Posted on Aug 20, 2023 1:59 AM

Top-ranking reply

Recycleur

Level 4

1,757 points

Posted on Aug 22, 2023 2:48 PM

That was a good team effort.

So here's the formula for E2 to identify the last three days with data.

=IFERROR(REFERENCE.NAME(OFFSET(B2,LARGE(OFFSET(D$2,0,0,ROW()−1),3)−ROW(),0,ROW()−LARGE(OFFSET(D$2,0,0,ROW()−1),3)+1)),"")

If you want a different number of past values than 3, change the two 3's by that number. If you do that often or have many columns with different scenarios, you may want to set a separate cell with the number of days and refer to it in the formula instead. And to make it fancy you also assemble the title in C1 accordingly. Just sayin'.

Still, in D2:

=IF(B2="-","",ROW())

And finally the actual calculation in C2:

=IF(OR(B2="-",E2=""),"",AVERAGE(INDIRECT(E2)))

The construction of the formula in cell C2 and the pre-mashed range reference in E2 make it easier to replace with another range formula like SUM, SUBTOTAL, even XLOOKUP and others, etc.

Under the table in the picture are the formulas for C2, D2 and E2, same than listed above, which you copy down to the bottom of the table.

View in context

iCloud Numbers formula over filtered rows ex.SUBTOTAL(101;A1:A10)

Similar questions