Formula Help

[Liz J]

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!

 

[ShaneDevenshire]

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...

 

[ShaneDevenshire]

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.