RE: Formula Help
- From: ShaneDevenshire <ShaneDevenshire@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 8 Nov 2008 09:21:01 -0800
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!
- References:
- Formula Help
- From: Liz J
- Formula Help
- Prev by Date: RE: VB Modules
- Next by Date: Re:How do I find skuo11.cab on preinstalled software?
- Previous by thread: Formula Help
- Next by thread: RE: Formula Help
- Index(es):
Relevant Pages
|
