How do I take a total value and reduce the value of a cell and move to next until the total is met?

[Knockoutpie]

It's difficult to describe, but could be considered something like a first in first out calculator.

There must be an easier way to do this than manual subtraction.

For this example the total inventory is 7500, I Would like to find how much was under 25, and if the total isn't met then move to next column under 10, etc using the next cell until the total inventory is met.

Example
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Item#[/TD]
[TD]Total Inventory[/TD]
[TD]25[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Part Example[/TD]
[TD]7500[/TD]
[TD]7150[/TD]
[TD]1800[/TD]
[TD]7600[/TD]
[TD]32950[/TD]
[/TR]
</tbody>[/TABLE]

Final Result
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Item#[/TD]
[TD]Total Inventory[/TD]
[TD]25[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Part Example[/TD]
[TD]7500[/TD]
[TD]7150[/TD]
[TD]350[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Maybe something like this:

Book1
	A	B	C	D	E	F
1	Item#	Total Inventory	25	10	5	0
2	Part Example	7500	7150	1800	7600	32950
3
4	Final Result
5	Item#	Total Inventory	25	10	5	0
6	Part Example	7500	7150	350	0	0
Sheet6

Cell Formulas
Range	Formula
C6	=MIN(C2,2*$B6-SUM($B6:B6))

Put the C6 formula in and drag to the right.

 

[Knockoutpie]

Maybe something like this:

	A	B	C	D	E	F
Item#	Total Inventory
Part Example
Final Result
Item#	Total Inventory
Part Example

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]7500[/TD]
[TD="align: right"]7150[/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"]7600[/TD]
[TD="align: right"]32950[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]7500[/TD]
[TD="align: right"]7150[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C6[/TH]
[TD="align: left"]=MIN(C2,2*$B6-SUM($B6:B6))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the C6 formula in and drag to the right.

Hi Eric,


It looks like your formula is reference both my example and what i'd like the outcome to be.

In this case the formula would have to be something along the lines of =MIN(B2,2*$B2-SUM($B2:F2)) (everything in row 2)

The result of this formula is not correct. But this is the correct path I would like to take.

 

[Eric W]

This is why I said "maybe something like this". I'm not entirely sure what your layout is like. Based on what you posted, it appeared that the top grid showed how many items were in each "bucket". Then the next grid showed an individual result.

However, it seems like you meant that the 2 grids referred to the same range, but you wanted that range to turn into what the second grid looks like. If that's the case, you might be looking more at a VBA solution. Possibly a macro that looks at your parts sheet and adjusts all of them as desired.

If that's the case, let me know.

 

[Knockoutpie]

This is why I said "maybe something like this". I'm not entirely sure what your layout is like. Based on what you posted, it appeared that the top grid showed how many items were in each "bucket". Then the next grid showed an individual result.

However, it seems like you meant that the 2 grids referred to the same range, but you wanted that range to turn into what the second grid looks like. If that's the case, you might be looking more at a VBA solution. Possibly a macro that looks at your parts sheet and adjusts all of them as desired.

If that's the case, let me know.

Hey Eric,

Both tables are referring to the same range.

VBA solution would be perfectly fine with me. I appreciate your assistance.

 

[Eric W]

Open a COPY of your workbook. I assume a starting range like this:

Book1
	A	B	C	D	E	F
1	Item#	Total Inventory	25	10	5	0
2	Part Example	7500	7150	1800	7600	32950
3	Example 2	5000	4000	400	300	800
4	Example 3	6000	3000	2000	500	400
Sheet4

Now press Alt-F11 to open the VBA editor. Press Alt-IM to insert a module. Paste the following code into the window that opens:

Sub CalcInventory()


    For r = 2 To Cells(Rows.Count, "B").End(xlUp).Row
        amt = Cells(r, "B")
        For c = 3 To 6
            inv = Cells(r, c)
            m = IIf(amt < inv, amt, inv)
            Cells(r, c) = m
            amt = amt - m
        Next c
        If amt > 0 Then Cells(r, "G") = "Extra: " & amt
    Next r
    
End Sub

Press Alt-Q to exit the editor. Now on your inventory sheet press Alt-F8. Select CalcInventory and press Run. You should end up with:

Book1
	A	B	C	D	E	F	G
1	Item#	Total Inventory	25	10	5	0
2	Part Example	7500	7150	350	0	0
3	Example 2	5000	4000	400	300	300
4	Example 3	6000	3000	2000	500	400	Extra: 100
Sheet4

Let us know if this is what you want.