Hello,
I'm a regular follower of Chandoo & I thank all contributors for my improved Excel Skills. Recently I faced a problem in Excel and would request your inputs to solve it in a simple way.
Our company has 70 products that are sold across 50 states in US through 1000s of agents who charge different rates (within a range) to sell each product. I've been asked to run an analysis to identify cost of agent across each state, each product over the last quarter month-on-month. Specifically, I've identified min, max, average for all of these by creating a simple pivot table. I got stuck while calculating median and mode. These options aren't available on pivot table and I have to work on the raw data.
What I have tried: Sorted data by product code (SKU)& State. At the change of every state under every SKU, I've manually added two rows to select the above number of orders and calculate median and mode. It is painful to do this 70*50 times approximately. That too when the source/range for the formula keeps changing. Is there a simple way to calculate the median and mode when no. of orders keep changing from state to state and product to product. Is there a way to let a formula dynamically select a range to calculate a value? Can this be done without VB?
I'm a regular follower of Chandoo & I thank all contributors for my improved Excel Skills. Recently I faced a problem in Excel and would request your inputs to solve it in a simple way.
Our company has 70 products that are sold across 50 states in US through 1000s of agents who charge different rates (within a range) to sell each product. I've been asked to run an analysis to identify cost of agent across each state, each product over the last quarter month-on-month. Specifically, I've identified min, max, average for all of these by creating a simple pivot table. I got stuck while calculating median and mode. These options aren't available on pivot table and I have to work on the raw data.
What I have tried: Sorted data by product code (SKU)& State. At the change of every state under every SKU, I've manually added two rows to select the above number of orders and calculate median and mode. It is painful to do this 70*50 times approximately. That too when the source/range for the formula keeps changing. Is there a simple way to calculate the median and mode when no. of orders keep changing from state to state and product to product. Is there a way to let a formula dynamically select a range to calculate a value? Can this be done without VB?
