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

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.

JL

Hi Johan,

Badunit wrote:

It appears the "101" code ignores manually hidden rows but not rows that are hidden by a filter.

I tried a new approach, following Badunit's suggestion of a simple AVERAGE.

Make a copy of the data table. Here we go with the copy before applying a filter:

Formula in A2: IF(Data::A2="","",Data::A2)

Fill down and right.

Now apply a filter to the copy table

Select All and Copy

In another table (Monday to Friday), click once in cell A1 and Menu > Paste Formula Results

Formula in C4: IF(B4="","",AVERAGE(B2:B4))

Fill Up to C2 and Fill Down to the bottom of column C.

It is important to start with C4 and Fill Up and Fill Down. You will see as you click on various rows in column C how the formula adjusts.

If the Copy for Filter table has blank rows at the bottom, these will fill and become visible as you add entries to the Data table.

Regards,

Ian.

Replying to myself, thinking that another range-defining formula could be much easier to understand. I couldn't let it rest. Basically it independently identifies the two extremity cells and concatenate their references names (adresses).

In the new multi-day average table I added multiple $ to simplify formula copying from one column to the next. So once you have filled column C, you can drag copy to D and E. Same for G to H and I.

In G2 (E2 in previous example). If you want to use a fixed number of days, replace G$1 by that number.

=IFERROR(REFERENCE.NAME(OFFSET(LARGE(OFFSET($F$2,0,0,ROW()−1),G$1),0,COLUMN($B2)−COLUMN($F2)))&":"&REFERENCE.NAME($B2),"")

Same than before in F2 (D2 in previous example)

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

Almost same in C2, just reference moved to column G instead of E

=IF(OR($B2="-",G2=""),"",AVERAGE(INDIRECT(G2)))

If you want to hide index and range columns but still use variable numbers of days, just put the number of days somewhere else and replace G$1 by the new location address.

If you don't want multiple day averages, still fill formulas in columns C, F and G then delete columns D and E. Everything will fall into places.

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

JL

One more way. Similar to Recycleur's except it averages the most recent three values for each row and uses a different formula. It still requires 2 extra columns.

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

D4 =LARGE(OFFSET(C$1,1,0,ROW(cell)−1,1),3)

E4 =IFERROR(IF(OR(B4="-",B4=""),"",AVERAGE(OFFSET(B$1,D4−1,0,C4−D4+1,1))),"Not 3 days to average")

Fill down with all to the end of the table

There are not three days to average until row 4 so columns D and E do not have formulas until row 4. If the first few rows in column B have dashes, rows 4, 5, or maybe lower might also not have three values to average. The column E formula will catch that and say "not 3 days to average".

The challenge is to identify the next three values to average.

Strategy: obtain the row number of only the cells that contain market values and then make an average of the values on three smallest row numbers from the current row to the bottom of the table.

I added 2 columns D and E to the table, which you can hide after setting this thing up.

In D2, to identify the rows with market data. When value is - the day is not included, which are weekends and other holidays.

(English and Swedish versions)

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

=OM(B2="-","",RAD())

In E2, to get the range from the current row to the bottom row. Self-adjusts when rows are added or removed. Included to avoid repetitions since it's used three times in the C column's formula.

=REFERENCE.NAME(OFFSET(D2,0,0,ROWS(D)−ROW(cell)+1),0,1)

=REFERENS.NAMN(FÖRSKJUTNING(D2,0,0,RADER(D)−RAD(cell)+1),0,1)

In C2, to calculate the average on the current day's value and the next two's:

=IF(B2="-","",AVERAGE(OFFSET(B2,0,0,1+IFERROR(SMALL(INDIRECT(E2),3),LARGE(INDIRECT(E2),1))−SMALL(INDIRECT(E2),1))))

=OM(B2="-","",MEDEL(FÖRSKJUTNING(B2,0,0,1+OMFEL(MINSTA(INDIREKT(E2),3),STÖRSTA(INDIREKT(E2),1))−MINSTA(INDIREKT(E2),1))))

Copy down the formulas in C2:E2 to the bottom of the table.

If you don't like the blue triangles indicating that the average's range include non-numerical cells values, then add another column to copy the results and hide the calculation column, C in my table.

It appears the "101" code ignores manually hidden rows but not rows that are hidden by a filter. But I don't think that matters because I think it will not work for you anyway. If you are using it on a range such as A4:A6 to average the most recent three days and if one of those rows has a dash in it, it is not going to expand the range to A3:A6 or A2:A6 to pick up rows that do not have dashes. If A4:A5 are Saturday and Sunday, it will not expand the range to A2:A6 to pick up Thursday and Friday.

If you want to average the range A1:A10, ignoring the cells with dashes, a simple AVERAGE will work. AVERAGE ignores text and will average the numbers only.

If you want to average the three most recent entries that are not dashes, I think that is going to take some extra columns to do some of the work. I do not have a solution for this at the moment.

Hello Hej Kopernikus1970,

The function SUBTOTAL is new to me. I tried this:

For a three day average, start in C4 with this formula:

SUBTOTAL(101,B2:B4)

Fill down.

Please reply with questions.

In Swedish:

DELSUMMA(101;B2:B4)

I hope that my understanding of SUBTOTAL and my conversion from English to Swedish are both correct.

Regards,

Hälsningar,

Ian.

First you have to identify what is the last day to which you want to compare today's close value. It's not necessarily the immediate day before because it could be a Sunday.

So we'll use the previous range-finding formula, simplified because we need only one day. First enter these formulas on row 3, it will make it easier to see what happens. Make sure you get all the $ at the right places so you can shift the columns around afterwards.

In the "Range gain&loss" column:

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

If you paste this directly into the cell you will get syntax errors, just select the $Index $Tue 27 Jun 2023 and similar bold parts and click on the appropriate cells to replace the references. In my table at this moment 27 Jun is the first data line (row 2), and obviously 28 Jun is the second one (row 3). See the last picture for reference. For those reading this first, look at previous posts to find what is the Index column.

Then the formula for gain becomes:

=IF($Close Wed 28 Jun 2023="-","",IF($Close Wed 28 Jun 2023>INDIRECT($'Range gain&loss' Wed 28 Jun 2023),$Close Wed 28 Jun 2023−INDIRECT($'Range gain&loss' Wed 28 Jun 2023),""))

and for losses (just copied from gains and lazily changed the >, - and + to avoid moving parts around)

=IF($Close Wed 28 Jun 2023="-","",IF($Close Wed 28 Jun 2023<INDIRECT($'Range gain&loss' Wed 28 Jun 2023),−$Close Wed 28 Jun 2023+INDIRECT($'Range gain&loss' Wed 28 Jun 2023),""))

I didn't put the gain and loss formulas in row 2 to avoid errors. If you must you can wrap them in IFERROR.

If you want to show 0 instead of a blank cell when two consecutive closing prices are equal, you replace > by >= in the gain formula.

Final result:

Hi Johan,

Kopernikus1970 wrote:

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

Yes.

Regards,

Ian.

For those studying these offset formulas or who would stumble on this thread after a search, here are different versions that work as well. The advantage is that the average column doesn't show the blue warnings, and the simplicity of the formula makes it more adaptable to other situations.

In D2 (same than in the previous post):

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

In E2, where all the heavy lifting to find the range is now done. Again, always self-adjusting to the number of rows. The 3 near the end is where you enter the number of values to be included in the range.

=REFERENCE.NAME(OFFSET(B2,0,0,MAX(IFERROR(SMALL(OFFSET(D2,0,0,ROWS(index)−ROW(cell)+1),3),LARGE(index,1))−ROW()+1,1)))

In C2, where a simple formula can be used on the identified range:

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

Yeah, I noticed belatedly that it refers many times to "index", like in ROWS(index), which is the content of the header row of column D. Numbers does that substitution. See my pictures.

Either you replace index by D, or you name the header.

Of course I don't know if in your own table the index is in column D, but I think you get the idea. There are many specific references that you have to look for in my formulas, like B2 and D2, and adjust if necessary.

And here is a correction.

I wrote:

Formula in A2: IF(Data::A2="","",Data::A2)

Fill down and right.

Sorry, fill right is wrong. Formula in Copy for Filter B1: IF(Data::B2="","-",Data::B2)

That will hide rows that do not yet contain Values in column B.

Regards,

Ian.

Hi Johan

OK, I finally figured out what was wrong with the formula I posted. Here's the corrected version for cell E2.

=REFERENCE.NAME(OFFSET(B2,0,0,MAX(IFERROR(SMALL(OFFSET(D2,0,0,ROWS(index)−ROW()+1),3),LARGE(index,1))−ROW()+1,1)))

The problem was the word cell in ROW(cell), which was just a placeholder inserted by Numbers. Now, as long as column D has the header index it will work. I copied it back to my table to confirm.