Return a Prior Month's Value from within the Same Table?

[chasfh]

What I am actually trying to do is create a formula that will capture a stock's gain or loss by referring to the number of shares in a prior month within the same table, and then multiplying that by the price difference between this month and the prior month. I have included what the answer should be in the sample XL2BB table below.

I need to do this on the prior month's shares total because when I receive a dividend, it gets reinvested in more shares, and I don't want to do so on the current month's higher share total because I would be double-counting against a separate income spreadsheet we are keeping. That's why the should-be answer for DUHP is not simply this month's total minus last month's total.

Since I am trying to return a column's value based on lookups of other columns, I tried doing this with XLOOKUP, but since the lookup values and the lookup array are all the exact same columns, I get the #SPILL error. So, if this can be done, I assume it will entail use of a different formula.

Seems self-explanatory, but let me know whether I need to clarify. Thanks.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Period	Ticker	Shares	Price	Total	Month	Year	M/M G/L		Answer should be
2	Aug-24	DRIV	200	22.71	$ 4,542.00	8	2024
3	Sep-24	DRIV	200	23.33	$ 4,666.00	9	2024			$ 124.00
4	Aug-24	DUHP	317.9095	33.56	$ 10,669.04	8	2024
5	Sep-24	DUHP	318.8494	33.91	$ 10,812.18	9	2024			$ 111.27
6	Aug-24	EMXC	73.21556	60.66	$ 4,441.26	8	2024
7	Sep-24	EMXC	73.21556	60.71	$ 4,444.92	9	2024			$ 3.66
Sheet1

Cell Formulas
Range	Formula
F2:F7	F2	=MONTH(A2)
G2:G7	G2	=YEAR(A2)
J3,J7,J5	J3	=C2*(D3-D2)

 

[Peter_SSs]

The $-127.27 figure is in cell Q7 of Post 13, in red.

Doh! Your whole mini sheet did not fit in my browser window and I hadn't scrolled right to see that. Sorry about that.

Would this small change do it? The only change is this
=LET(s,TAKE(SORT(FILTER(Table24[[Mo. End]:[Price]],([Mo. End]<=[@[Mo. End]])*([Account]=[@Account])*([Ticker]=[@Ticker])),1),-2),IFERROR(MIN(INDEX(s,0,4))*(INDEX(s,2,5)-INDEX(s,1,5)),""))

I have not used the Buy/Sell column. My logic was that if a SELL then use the current (smaller) number, if a BUY then use the previous (smaller) number and if neither SELL nor BUY then use either number (also = smaller) so always just use the smaller of current and previous number.
I had this logic in mind for this calculation when I asked before about whether you actually needed the Buy/Sell column.

chasfh.xlsm
	A	B	C	D	E	F				J				N
2	Mo. End	Account	Ticker	Shares	Price	Total				Buy/Sell?				Test2
3	May-24	Terrie	NICHX	388.5556	27.59	$10,720.25
4	Jun-24	Terrie	NICHX	388.5556	27.16	$10,553.17								-167.078908
5	Jul-24	Terrie	NICHX	388.5556	27.41	$10,650.31								97.1389
6	Aug-24	Terrie	NICHX	388.5556	27.57	$10,712.48								62.168896
7	Sep-24	Terrie	NICHX	282.8209	27.12	$7,670.10				Sell				-127.269405
8	May-24	CP-Managed	NPSRX	395.131	15.25	$6,025.75
9	Jun-24	CP-Managed	NPSRX	397.0937	15.3	$6,075.53								19.75655
10	Jul-24	CP-Managed	NPSRX	399.0489	15.44	$6,161.32								55.593118
11	Aug-24	CP-Managed	NPSRX	400.9902	15.62	$6,263.47								71.828802
12	Sep-24	CP-Managed	NPSRX	383.9396	15.81	$6,070.09				Sell				72.948524
Sheet3

Cell Formulas
Range	Formula
N3:N12	N3	=LET(s,TAKE(SORT(FILTER(Table24[[Mo. End]:[Price]],([Mo. End]<=[@[Mo. End]])*([Account]=[@Account])*([Ticker]=[@Ticker])),1),-2),IFERROR(MIN(INDEX(s,0,4))*(INDEX(s,2,5)-INDEX(s,1,5)),""))

 

[chasfh]

Again, @Peter_SSs, just great, great work. That seems to have solved it.

It's a more elegant solution to add the MIN in there because, you're right, the formula shouldn't have to depend on what's in the BUY/SELL column. The only possible glitch that might come up is when a stock is both bought (whether purchase or dividend reinvestment) and sold within the same month, which may be an issue since this table summarizes an entire month into one line.

I am fascinated by the formula, which employs a lot of command I had never used. I think I was able to figure out how LET, TAKE, SORT, and FILTER work. I am trying to get my arms around exactly how SORT and INDEX works in this case.

I understand SORT sorts the contents of a range or array in ascending or descending order, and that values can be sorted by one or more columns. How exactly is that working here? Month, Account, and Ticker can all be sorted individually—how does SORT determine sorting when it's in the formula as an array?

I know INDEX returns the value at a given location in a range or array, and can retrieve individual values, or entire rows and columns. I get that the "s" refers to the formula shortened by LET; I get that "4" is column 4 and "5" is column 5 in the table; and I get the math part. Can you help me understand how the "0" and "2" and "1" within works, precisely?

I really appreciate your work on this, and thanks in advance for helping me understand what's under the hood (or "bonnet", as the case may be) of the formula!

 

[Peter_SSs]

Hmm, let's try by looking at how the result in row 7 is obtained

=LET(s,TAKE(SORT(FILTER(Table24[[Mo. End]:[Price]],([Mo. End]<=[@[Mo. End]])*([Account]=[@Account])*([Ticker]=[@Ticker])),1),-2),IFERROR(MIN(INDEX(s,0,4))*(INDEX(s,2,5)-INDEX(s,1,5)),""))

s is just a name/variable that takes its value from the red part of the formula. I think that you already have figured most of this but it takes the first 5 columns of the table Table24[[Mo. End]:[Price]] and filters that for rows that have a date <= the date in row 7 ([Mo. End]<=[@[Mo. End]]) and Account the same as the Account in row 7 ([Account]=[@Account]) and Ticker the same as the Ticker in row 7 ([Ticker]=[@Ticker])
Once that FILTER is done we would have this

That data is then SORTed by the first column, which in this case does not change anything since those dates are already in order.
Next is TAKE(...., -2) which takes just the last 2 rows giving this ..

which must be the current row and the most recent previous date row.

So now we get to the blue bit where s is the 2-row table above.
INDEX(s,0,4) The 0 means take all rows and the 4 means from column 4 (Shares column). MIN obviously takes the smallest value (per my logic outlined in post 21)
This is multiplied by (INDEX(s,2,5)-INDEX(s,1,5)) being the 2nd row col 5 (current price) minus 1st row col 5 (price in the most recent previous row)
The IFERROR is there to catch the case where there is not 2 rows in the "s" table (ie no previous data exists for that account & ticker)