Sequential Calculations

[Rik76]

Hi,

I'm have a large data set and am trying to identify a formula that will make a calculation based on the sequence number. Example below.

From a lookup I have identified that a quantity of 3 are packed, but I want the 3 to be deducted from the first schedule line only, and if the schedule quantity is less than the packed quantity, to deduct the remainder from the next sequence and so on.

In the case of the below, in the sequence qty column, the results should read: 2 for sequence 1, 1 for sequence 2 and 0 for sequence 3.

Anyone have any ideas? I'm hoping its possible! Thanks in advance to anyone that can help

ORDER No_ITEM No Total Order Quantity Sequence Number Sequence Qty Packed Qty 6075_10 6 1 2 3 6075_10 6 2 1 3 6075_10 6 3 3 3 <colgroup><col><col><col><col><col></colgroup><tbody> </tbody> <strike></strike>	<strike></strike>

<tbody>
</tbody>

 

[Rik76]

so packed quantity is really "stock level" and sequence is really "order quantity"

so as you produce more stock, stock level rises, and as you dispatch orders stock level diminishes. Planned stock dispatch is scheduled according to customer requirements and is either dispatched on time or late ??? Not being pedantic, but still struggling to understand the issue !

Don't think you're being pedantic at all; I really appreciate you taking the time to help. I guess to simply what I'm trying to achieve, I'll use another example as I may be over complicating things for the purposes of explanation (I've been known to do that! )

STEP 1: LOOKUP value in column A in another table. This returns a value of 3
STEP 2: Take the value in column E and deduct the returned value of 3 from the first sequence number in column C. As the value of 3 if greater than the value in column D for the first sequence, go to the next sequential number (2) and deduct the remaining value from that sequence. Continue until the full returned value of 3 has been deducted from one of more lines as necessary to deduct the full qty.

ORDER No_ITEM No	Total Order Quantity	Sequence Number	Sequence Qty	Packed Qty
6075_10	6	1	2	2
6075_10	6	2	1	1
6075_10	6	3	3	0

<tbody>
</tbody>

 

[oldbrewer]

Take the value in column E and deduct the returned value of 3 from the first sequence number in column C.

ie take 2, and deduct 3 from sequence number in col C

ie take 2 and deduct 3 from 1

did you mean sequence quantity ? Sorry I just don't get it......

 

[Rik76]

Take the value in column E and deduct the returned value of 3 from the first sequence number in column C.

ie take 2, and deduct 3 from sequence number in col C

ie take 2 and deduct 3 from 1

did you mean sequence quantity ? Sorry I just don't get it......

[h=3]What I am ultimately trying to do, is when there is more than one sequence line for an item, I want to distribute the total "packed" quantity (whatever that may be) for the item across the schedule lines according to the sequence. [/h]

 

[oldbrewer]

so if 5 sequence lines and there are 35 packed items, assign 7 to each sequence ?

 

[Rik76]

so if 5 sequence lines and there are 35 packed items, assign 7 to each sequence ?

The sequence qty determines how many are assigned.....so in the example, as total 3 are packed, sequence 1 calls for qty 2, so 2 would be assigned, with 1 left over. As there is a left over qty, it then goes to sequence 2 and assigns to that, which in this instance is 1.

Sequence Number	Sequence Qty	Packed Qty
1	2	2
2	1	1
3	3	0

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

Using the example of 5 sequence lines, with 35 packed I would expect to see the following (based on the sequence qty for each line):

Sequence Number	Sequence Qty	Packed Qty
1	10	10
2	5	5
3	7	7
4	11	11
5	5	2

<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>
</tbody>

 

[oldbrewer]

starting / running packed quantity	Sequence Number	Sequence Qty	Packed Qty	additional packed quantity	running unallocated packed quantity		row 3
35	1	10	10	0	25		row 4
25	2	5	5	0	20
20	3	7	7	0	13
13	4	11	11	18	20
20	5	5	2	0	18
col a	col b	col c	col d	col e	col f
		the first 10 in packed quantity derived by
		=IF(A4-C4>0,C4,A4-C4)
		the first 25 in running unallocated packed quantity derived by
		=A4-D4+E4

<colgroup><col><col><col><col><col><col><col span="5"></colgroup><tbody>
</tbody>

 

[oldbrewer]

starting / running packed quantity	Sequence Number	Sequence Qty	Packed Qty	additional packed quantity	running unallocated packed quantity		row 3
35	1	10	10	0	25		row 4
25	2	5	5	0	20
20	3	7	7	0	13
13	4	11	11	18	20
20	5	5	2	0	18
col a	col b	col c	col d	col e	col f
		the first 10 in packed quantity derived by
		=IF(A4-C4>0,C4,A4-C4)
		the first 25 in running unallocated packed quantity derived by
		=A4-D4+E4

<colgroup><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>