Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help
I am looking at an order file with approximately 3000 sales orders. Column J
is the item #, and column P is the order quantity (in pieces). I want to manipulate this data such that the file will show units per order by item # within a range of increments of 5 pieces: 0-5 pieces, 5-10 pieces, and so on, without adding the order quanities as would happen with pivot table. For example, I would want to have something like this 0-5pcs per order 5-10pcs per order 10-15pcs per order Item # 1 2 3 4 Could I use a formula? Or, is there a better route to manipulate this data? My goal is to find out, based on historical orders, how much of an item number customers are buying at a time. So, based off of properly grouping this data, I might conclude that the majorty of the orders for item # 1 are made in quantities of 16psc, item # 2, majority of orders are made in quanities of 7, and so on. I would appreciate any suggestions on how excel could benefit my research. Thanks for your help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help
Hi,
Try this =SUMPRODUCT(--($J$2:$J$25=$A2),--($P$2:$P$25<=B$1)) This assumes you output table starts in A1 with labels in the first column and first row. Also instead of 1-5, just enter the upper value in B1:E1. If you want to display it as 1-5 you can use a custom format. So the top row reads 5 , 10, 15.... A minor point you have overlapping ranges in your example 1-5 and 5-10... -- Thanks, Shane Devenshire "Liz J" wrote: I am looking at an order file with approximately 3000 sales orders. Column J is the item #, and column P is the order quantity (in pieces). I want to manipulate this data such that the file will show units per order by item # within a range of increments of 5 pieces: 0-5 pieces, 5-10 pieces, and so on, without adding the order quanities as would happen with pivot table. For example, I would want to have something like this 0-5pcs per order 5-10pcs per order 10-15pcs per order Item # 1 2 3 4 Could I use a formula? Or, is there a better route to manipulate this data? My goal is to find out, based on historical orders, how much of an item number customers are buying at a time. So, based off of properly grouping this data, I might conclude that the majorty of the orders for item # 1 are made in quantities of 16psc, item # 2, majority of orders are made in quanities of 7, and so on. I would appreciate any suggestions on how excel could benefit my research. Thanks for your help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help
Hi,
I can produce exactly the same result using a pivot table: 1. Select your data and choose Data, Pivot Table & Pivot Chart Report, Next, Next, 2. On the 3rd step of the wizard choose Layout 3. Drag Order Number to the Row area, Pieces to the Data area, and Pieces to the Column area. 4. Double-click the Pieces field button in the Data area and change the calculation to Count, OK. 5. Click OK, and Finish. 6. In the pivot table select the Column field (Pieces) and choose the command PivotTable, Group and Show Detail, Group 7. In the Grouping box set the lower and upper number, for example 1 and 20, and then set by to 5. 8. You can remove the grand totals if you want using the Pivot Table, Options command. If this helps, please click the Yes button. -- Thanks, Shane Devenshire "Liz J" wrote: I am looking at an order file with approximately 3000 sales orders. Column J is the item #, and column P is the order quantity (in pieces). I want to manipulate this data such that the file will show units per order by item # within a range of increments of 5 pieces: 0-5 pieces, 5-10 pieces, and so on, without adding the order quanities as would happen with pivot table. For example, I would want to have something like this 0-5pcs per order 5-10pcs per order 10-15pcs per order Item # 1 2 3 4 Could I use a formula? Or, is there a better route to manipulate this data? My goal is to find out, based on historical orders, how much of an item number customers are buying at a time. So, based off of properly grouping this data, I might conclude that the majorty of the orders for item # 1 are made in quantities of 16psc, item # 2, majority of orders are made in quanities of 7, and so on. I would appreciate any suggestions on how excel could benefit my research. Thanks for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|