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

Similar questions

Subtotal filtered data Is there any way to sum data in a column that is filtered data in Numbers? I have a time tracking spreadsheet that I imported from Excel. The one issue I have not been able to resolve is a subtotal for filtered data. The filter hides data, and a sum formula adds values whether or not they are hidden. In Excel I use the subtotal function to sum filtered data, but that formula is not available in Numbers. The data I am filtering varies from project to project, so it doesn't lend itself to categories. Typical tasks include "Prepare report," "Edit report," "Review case file," "Respond to email questions from attorney," and "Teleconference with attorneys." I can easily filter the data by selecting one or more of these entries, but I haven't yet found a way to sum the durations for the filtered set (not counting workarounds involving pasting the filtered cells and summing their contents).
2764 5

31 replies

Recycleur

Level 4

1,757 points

Aug 22, 2023 6:04 AM in response to Badunit

Hi Badunit

Good answer too, but you didn't notice that the OP wanted the average for the NEXT three days, not the past three. So on January 1st it shall show the average for the 1st, 2nd and 3rd, not the other way around.

Also, I learned a lesson for a previous post of mine, is to remove all optional parameters from formulas before copying and pasting them in a post. Like ROW(cell) creates a syntax error when pasted in a cell. It should be ROW(). From now on I will always do the exercise of pasting back the formula text in my own spreadsheet to make sure I don't repeat this error.

Cheers.

Recycleur

Level 4

1,757 points

Aug 22, 2023 6:22 AM in response to Recycleur

(EDIT) Well it's not clear actually: at one point he says "average of three earlier days", while further he says that "Thursday it will calculate Thursday, Friday and Saturday". Johann, can you clarify?

Aug 22, 2023 11:48 AM in response to Badunit

I have now got your formulas working in my numbers. really smooth to only change language and location.

If I wish to use this formula to take average back in time where shall I change?

=REFERENCE.NAME(OFFSET(Value 'Tuesday, 1 August 2023',0,0,MAX(IFERROR(SMALL(OFFSET(Index 'Tuesday, 1 August 2023',0,0,ROWS(Index)−ROW()−1),3),LARGE(Index,1))−ROW()−1,1)))

And where am I change to get more days back in time, ex 7 days? As i mentioned in the beginning my fx skills is not the best. But I'm trying to teach me.

Thank you all Badunit, Yellowbox and Recycleur for your patience and help!

Recycleur

Level 4

1,757 points

Aug 26, 2023 7:56 AM in response to Kopernikus1970

Hi Johan

Thanks for the opportunity to develop this solution for you. Frankly I learned something new as I went, so it's good for everybody. Please mark one of posts as Helpful so it appears right below your initial question.

I used your model to plot a similar table for myself. That is fantastic. Regards.

Aug 27, 2023 4:55 AM in response to Recycleur

I need to ask you about one more question Recycleur.

You mention above that the formula in cell C2 was constructed to change the range formula. I have tried to do that but obviously i made wrong due to the syntax error i´ve got.

In the Gain column I will calculate

=If ("todays closing" >"yesterdays closing","todays closing"-"yesterdays closing","")

Thank you in advance!

Recycleur

Level 4

1,757 points

Aug 27, 2023 10:58 AM in response to Kopernikus1970

In the first formula, the blue bubble must always refer to the first cell of the column, i.e. $Index $Thursday, 1 June 2023, both parts with $. It defines the fixed top of the search range from which the second largest value is identified.

Clearly, from the line of 4 July in my own table starting on 27 June:

=IFERROR(REFERENCE.NAME(OFFSET(LARGE(OFFSET($Index $Tue 27 Jun 2023,0,0,ROW()−1),2),0,COLUMN($Close Tue 4 Jul 2023)−COLUMN($Index Tue 4 Jul 2023))),"")

Sep 8, 2023 7:12 AM in response to Recycleur

Hi it's me again. I hope I can ask you one more question?

From your example and help above Recycleur I have now a great file. But one last struggle thing, I hope.

What I wish to do is for ex SUM("Close20230120"-"10 working days back"). I have changed the formula so I can do 1 day back, and it also works over non working days. But I can't get it work using the value in "Momentum Period = 10 and to get a 10 days back sum.

Aug 21, 2023 10:18 PM in response to Yellowbox

What a great engagement here , thank you :-)

before i try your suggestion Ian i must ask.

do i need to make a manual copy of data every day between the copy for filter and monday to friday?

Badunit

Level 7

31,351 points

Aug 22, 2023 6:28 AM in response to Recycleur

Yeah, the OP says two opposite things. Typically you average previous days not future days so I went with that.

Aug 22, 2023 10:09 AM in response to Badunit

Sorry if I was confusing in my text.

It is average three working days back in time i wish to have.

I haven't tried with your latest suggestions yet. I will do that and came back after that.

Thank you

Aug 26, 2023 5:48 AM in response to Recycleur

Weekend and a fresh brain together with all your help solve my issue. Thank you :-)

Now I hope my file will update like Apple Numbers Stock portfolio template do. Well see.

Aug 27, 2023 1:05 AM in response to Recycleur

Lovely,

and here is the url with explanations for %K and %D i use in my Stochastics oscillator. https://investexcel.net/how-to-calculate-the-stochastic-oscillator/

Recycleur

Level 4

1,757 points

Aug 27, 2023 5:24 AM in response to Kopernikus1970

(deleted, posted too early by accident)

Aug 27, 2023 10:28 AM in response to Recycleur

I follow your example but can't get it work? If you take a look in Range gain&loss column, the range is calculated wrong if you compare to the date column. But I can't see what I have mede wrong?

Aug 27, 2023 11:32 AM in response to Recycleur

Thank you Recycleur that you are a great teacher! :-)

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

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