This is exactly the kind of question that reveals why traditional spreadsheets are holding people back. The user is asking for a straightforward business need: rank each customer's product sales without leaving their main table. They have correctly identified that RANK.EQ is the right function, but they are hitting a wall because the tool requires them to manually define a range for every single customer. That is not a user error. That is a tool limitation.
The practical problem here is that the user is being forced to think like a database administrator when they just want to think like a business analyst. They want to say, "For each customer, rank their own products by sales value." That is a simple, human-centered request. But the spreadsheet demands they break that request into hundreds of separate range definitions, one per customer. The user is smart enough to suspect there is a better way, and they are right. The solution exists, but it does not exist inside the traditional cell-and-formula paradigm they are working in.
What this user needs is a system that understands context. An AI-native spreadsheet can look at that customer number column, recognize it as a grouping identifier, and apply the rank logic automatically within each group. No manual range selection. No copy-paste errors. No separate helper columns that break when a new customer is added. The user's intuition about making the "Range" selective is exactly the right insight; it is just that the tool they are using cannot fulfill it without complexity.
This is what we mean when we say the future of data management is about empowering users, not teaching them workarounds. The user should be able to ask for what they need in plain terms and get a result that respects their data structure. If you are spending time wrestling with how to make RANK.EQ work across groups, you are not doing analysis. You are fighting the tool. The better path is to explore a platform that treats your customer column as the natural grouping it already is, and lets you focus on what the ranking tells you about your business.