This is a sharp technical problem, and the request itself reveals something important about how people actually use spreadsheets. The user wants a dynamic Pareto chart, one that recalculates the 80/20 split correctly when filtering by sales rep. That means the first row of data changes depending on which rep is selected. Traditional spreadsheet formulas, which assume a fixed first row, break under that condition. The user is describing a real workflow constraint, not a hypothetical edge case.
The core issue is that standard spreadsheet logic treats row references as static. When you write `=C5` in B5, you are locking that formula to row 5. If filtering moves a different row to the top, that formula no longer captures the correct starting value. The user has already identified the workaround: they need a way to return the value from the row above *as it appears after filtering*, not as it was originally written. This is not a bug in their thinking; it is a limitation of the tool they are using.
What this means in practice is that building an accurate filtered Pareto chart requires either manual recalculations after every filter change or a more intelligent approach to row referencing. The user is essentially asking for a formula that respects the visible order of data, not the underlying row number. In a traditional spreadsheet, that requires either an `AGGREGATE` or `SUBTOTAL`-based solution that recalculates based on visible rows, or a helper column that dynamically ranks filtered data. Neither is intuitive for most users, and both add complexity to what should be a straightforward analysis.
Our view is that this problem highlights a gap between how spreadsheets are designed and how people actually need to use them. Filtering is a fundamental interaction, yet the formula language treats it as an afterthought. The user should not have to become a spreadsheet engineer just to build a chart that stays accurate when they change a filter. A truly modern tool would let you define a running total that automatically adjusts to visible rows, without requiring workarounds or helper columns. The solution is not a better formula trick, it is a spreadsheet that understands filters as a first-class part of the calculation model. That is the standard we should expect, and the one worth building toward.