The post from r/excel is exactly the kind of problem that should not exist in 2024. A user with a clear understanding of what they need, a weighted average cost basis for tax purposes, split across a ledger and a dashboard, is forced to manually construct formulas in a tool that treats this logic as advanced wizardry. The user even admits they are a beginner and would prefer a formula solution but will settle for a macro. That is not a skill gap. That is a product gap.
What this user describes is a fundamentally simple data operation. They have a transaction log with dates, tickers, operations, quantities, and prices. They need a dashboard that calculates average load price per ticker, accounting for buys and sells, and then computes unrealized P&L and projected tax liability. In a modern spreadsheet environment, that should be a single query or a few lines of array logic. Instead, the user is wrestling with weighted averages, FIFO confusion, and the limitations of Excel's table references. The fact that they are asking for help on Reddit, rather than simply typing their intent into the tool, tells you everything about how far traditional spreadsheets have fallen behind actual user needs.
This is where an AI-native approach changes the equation. Imagine opening a blank sheet, typing "track my stock portfolio with a ledger and a dashboard that calculates weighted average cost basis and unrealized P&L," and having the system build both sheets for you, complete with the correct formulas, dynamic ranges, and tax logic for your country. The user's manual entry of dates, times, and operations remains, but the calculation layer becomes a conversation, not a construction project. The formula for weighted average price, (sum of load values for buys minus sells) divided by (sum of quantities for buys minus sells), becomes something the tool generates, tests, and explains, not something the user has to debug across three forum threads.
The lesson here is not that this user should learn more Excel. It is that spreadsheets should meet them where they are. They already know what they want. They should not need to ask the internet how to build it. For anyone still manually calculating weighted averages in a ledger, or wondering whether FIFO applies to stocks, the answer is not a better formula. The answer is a tool that understands the problem before you finish typing it.