Trying to always compute top down in a single sortable table

Question

Level 1

13 points

Trying to always compute top down in a single sortable table

I have a table that needs to always be computed from the top row down, even as rows are resorted. This is because some calculations get reset when a condition is met (in the example below, when the number of Apple shares owned reaches 0, the cost basis is reset).

I got it to work using two tables but this is really inconvenient and I'm sure there's a way to do it in a single table. Basically I created a second table with a "chrono" column that numbers each row, then I got B2 to always return whatever is in B2 of the first table, C2 to mirror C2 etc. This way I can sort the first table, and the second one just re-fills without having its rows resorted (which broke my calculations if I everything was in one single table)

Is there a way to do all this using the table's row numbers instead of my "Chrono" column ?

Two columns make use of the chrono column

Column H:

IF(B2≠"",SUMIFS(M,Chrono,"<"&ROW()−1,Market,C2)+M2,"")

Column I :

IF(B2≠"",IFERROR(IF(B2="BUY",VLOOKUP(MAXIFS(Chrono,Chrono,"<"&A2,Market,C2),Chrono:'Total Cost

Holdings',9,FALSE)+(D2×E2),VLOOKUP(MAXIFS(Chrono,Chrono,"<"&A2,Market,C2),Chrono:J,10,FALSE)×H2),E2×D2),"")

iMac 27″, macOS 13.6

Posted on Apr 16, 2024 2:05 PM

Reply

Answer 1

Urbiter Author

Level 1

13 points

Apr 17, 2024 7:36 AM in response to Badunit

Sorry, I see I was not clear.

I'm really glad to know this can be done in one table.

My problem was that when resorting rows, for example when sorting ascending by "Market" so that all the Apple trades are shown together, my imperfect formula that reset the cost basis (when all shares of Apple are sold) would break. I guess my first question is what is the best or right way to do this cost basis reset? Can I do this without adding a column that numbers each row? You can see in the image below that my solution was to create an extra column where the header is 0 and each cell is the cell right above it +1 so that when I resort the table, this column never changes (always reads 1, 2, 3, 4 etc). I then use this ordering in my formula.

I also have another extra "Change" column that simply makes the number of shares traded negative when the row is a BUY rather than a SELL.

IF(C6≠"",SUMIFS('Change ',0,"<"&ROW()−1,Market,D6)+N6,"")

Regarding your note about dates, I always use the "Insert Current Time" feature which inserts both the date and the time, but have formatted it to only show the date. This way when I sort ascending I have no issues even with trades on the same day.

Reply

Answer 2

Urbiter Author

Level 1

13 points

Apr 17, 2024 8:46 AM in response to Badunit

If I wanted to eliminate the column with the ordering (with the header "0"), how could I get the below formula to work? I am referencing the row number with ROW(), but how can I specify al the rows as a range without creating this extra column ?

IF(C2≠"",SUMIFS('Change ',0,"<"&ROW()−1,Market,D2)+N2,"")

Reply

Answer 3

Badunit

Level 7

26,296 points

Apr 16, 2024 7:12 PM in response to Urbiter

I am not sure what you want. It is the phrase "computed from the top row down, even as rows are resorted" that is confusing me because I don't understand why you would want that. You are talking about wanting it all in the lefthand table so I am assuming you are referring to resorting the lefthand table. Right now with the lefthand table sorted in chronological order, the running total of Apple shares goes 1, 3, 0, 1. This makes sense. But if resorted descending by date (i.e., backward chronological), the running total computed from the top down will go -3, -2, -1, 1. Is that what you want? Or does "from the top down" mean "always chronological"? Either can be done in the one table.

Note: With "always chronological", two trades of the same stock on the same day will become melded together as one trade. If the table can be sorted by anything out of chronological order, there is no way to know which trade on the same date came first based solely on the date. For example, if you had 2 shares of apple then bought 2 more the next day and sold 2 on that same day, the running total will be 2, 2, 2. It will not be 2,4,2 or 2,0,2.

Reply

Trying to always compute top down in a single sortable table

Similar questions