Wondering if this is possible with a pivot table. I can do it with a formula in a table but the flexibility of pivots would be useful here if I could make it work.
The source data has various columns like name and year but also two columns with numerical values, say X and Y. I can get the max of X easily enough, but I want to show the minimum Y value where X is that max value. So if the max of X is 10, and there are 3 instances of this, I want the lowest Y value out of those 3 rows.
It's difficult to search for similar questions online but I've done my best and I'm thinking it can't be done.
Any advice would be appreciated!
The source data has various columns like name and year but also two columns with numerical values, say X and Y. I can get the max of X easily enough, but I want to show the minimum Y value where X is that max value. So if the max of X is 10, and there are 3 instances of this, I want the lowest Y value out of those 3 rows.
It's difficult to search for similar questions online but I've done my best and I'm thinking it can't be done.
Any advice would be appreciated!
