I have column A for Job Type. I have column B for Experience Range. I have column C for salary. These are in a sheet named 'Data'.
I have created a pivot table with Job Type as rows and Experience Range as columns. (in another sheet)
I want to find median for each cell in the Pivot Table.
How do I do that?
e.g. how do I find the median of Salary for all 'Stage Manager' (cell A2) with 'less than 2 years' (cell B1) experience?
Please help. Thank you!
As you've no doubt discovered, the median isn't listed on the grouping options (sum, min, max, count, etc).
There doesn't appear to be a direct way around the problem; however, you may be able to hack your way around it by adding custom a formula inside the table. Then use the median() function in the formula.
Another approach is abandon pivot tables and use array formulas instead.
You can use * for and and + for or. FALSE is coded as 0 and TRUE as 1.
0*1 = 1, so if only one condition is met it will be FALSE(AND)1+0 or 1+1 is >0, so if only one condition is met, it will still be >0 (OR)You can use this fact to do a test as to whether your condition is >0 and then transform your array, so that if the condition is true, the array is the array but otherwise it equal . This will basically eliminate all rows where the conditions are not met (making them count as blank in any array formula)
You can use this tick for a large number of AND and OR conditions.
If you use name manager to name your arrays, then you can very easily vary conditions very fast and create a simulated pivot table.
You can then use the aggregate function to ignore hidden rows (e.g. aggregate can calculate median, percentiles and other stuff), and then the filter function can effectively work as a pivot filter.
I usually make my first column a test column that tests if a row is hidden or not and returns 1 if visible and 0 otherwise. Then I build that condition into my condition stack to act as an additional AND switch. This means suddenly your formulas react to the filter to only consider values that are filtered in.
This stuff is really great to do multi-conditional calculations on a large amount of data without the need to manually calculate after each filter - the calculations happen on the fly, as you filter!
Calculations can take 3-5 seconds depending on how many conditions you have and the size of your raw data, so not quite as fast as a pivot table, but very convenient.
