Intelligently reduce numbers

[Tpiper1]

Hello
I'd like a create a formula that can reduce a number according to some logic.
A B C
5 5 5
6 4 5
7 3 5
8 2 5
9 1 5
10 0 5
9 0 6
8 0 7
....
0 0 15


Basically, I want each row to to calculate as the following
- If there is anything in column B, reduce it by '1' and add '1' to column A
- If column B is 0, check column A and reduce it by '1' and add '1' to column C
- If both Column B and A are 0, do nothing

Is this possible?

[shg]

	A	B	C	D	E
1	5	5	5		A1:C1: Input
2	6	4	5		A2: =A1 + IF(B1<>0, 1, -(A1>0))
3	7	3	5		B2: =B1 - (B1>0)
4	8	2	5		C2: =C1 + (B1=0)*(A1>0)
5	9	1	5
6	10	0	5
7	9	0	6
8	8	0	7
9	7	0	8
10	6	0	9
11	5	0	10
12	4	0	11
13	3	0	12
14	2	0	13
15	1	0	14
16	0	0	15
17	0	0	15
18	0	0	15
19	0	0	15

[alansidman]

Here is a VBA solution

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[Tpiper1]

Thank you so much for the help, it's really interesting to see whether a formula or macro is more appropriate
I'd like to see if you guys can handle some added complexity. I'm coming up with more ideas for what I'd like to do, but I am a terrible programmer. Any idea how to handle the following scenario and example?

LOGIC
ALWAYS LOAD SITE1 FIRST
ALWAYS TAKE FROM SITE2 FIRST
IF SITE2=0 THEN TAKE DIRECTLY FROM SITE3
IF NO SITE1 AVAILABLE, LOAD SITE2 FROM SITE3

Please Login or Register  to view this content.

[alansidman]

I don't understand what you are referring to when you use the term "Load". What is to happen with the data?

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Tpiper1]

My apologies, I meant load as in to increase. It's a zero sum model of moving 'inventory' around between locations.

[alansidman]

It would really beneficial if we see a mock up of what you have and what you want as I indicated in the earlier post.

[Tpiper1]

Here is the attachement. What I am trying to do is define a way to do the following:
1) input the rate at which an 'inventory' can move on a daily basis. This is entered by the user (me) in column D. It's important to note here that 'D' defines the maximum number of units that can be moved for the day (row)
2) define 3 buckets, column E H and M
3) have the workbook follow a few simple logic steps to move the 'inventory' (step 2) between the buckets, according to the rate in Column D (step 1)

The logic for prioritizing the rate is as follows as follows.
1) always prioritize moving inventory into column M, out H until H is at '0'. If H is empty, use E. If M is full, according to the max capacity defined in L, then move on to the next step. Note that I've used the example of 3 boats coming throughout the month.
2) If step 1 is complete and there is still capacity for the row (day) to move additional inventory, always prioritize moving from H into E

These should continue until the next truck/boat arrival, or until all inventory is in E. I'd like to understand this conditional programming better. This boils down to creating a system where H and M are transferring goods at a set rate, and E serves as a backup reserve tank.

[Tpiper1]

Still having some trouble with this one!

[XOR LX]

Hi,

It's all a bit confusing, I'm afraid. Could you manually add some of your desired results to that workbook to help clarify things, preferably with brief explanations behind each entry?

Regards

[Tpiper1]

test inventory.xlsx

I've cleaned it up to make it easier to understand, I hope. The text red color font are items that I would enter manually. Week to week, where I enter these values might change as the rows are date based.

As I described earlier, the items are basically moving inventory. One bucket is an outgoing bucket that departs whenever it is full. One bucket is an incoming bucket that departs whenever it is empty. The last bucket is a stockpile that is onsite and holds inventory.

[Tpiper1]

Bumping above all the SOLVED posts.

[Tpiper1]

Bumppping

[Tpiper1]

Trying to get to that front page again

[XOR LX]

I hate to say it, but it's still completely baffling to me.

Perhaps you need to try a different approach at explaining precisely what it is you are trying to do.

And I don't think it can just be me - this post has now had an astonishing 133 views so I can only presume that most visitors read your description and, like me, are bamboozled and quickly leave.

Regards

[Tpiper1]

I'll try and explain this as cleanly as possible:

a = incoming arrival bucket. I'm using the example of a truck for simplicity
b= outgoing departure bucket. I'm using the example of a boat for simplicity
c= reserve inventory bucket on hand. I'm using the example of a warehouse for simplicity.
x= physical units of inventory that are tracked in a, b, and c.
each row = a day

'a' can come in with 'X₁' amount of units. This could be a shipment incoming of 1 thing to 10000000 things. It varies
'b' can leave with 'X₂ amount of units. This could be a shipment outgoing of 1 thing to 10000000 things. It varies

'Y' is the rate that the warehouse worker can move inventory. Because he is just one person with 2 hands, he can only move a finite amount of units. If this is 'y'=5 units per day, he can move 5 things. If he moved 5 things off of the truck 'a' onto the boat 'b', then he is done and his day is over. He CANNOT both move 5 units out of 'a' and onto 'b' WHILE ALSO moving 5 units from 'c' onto 'b'. It's worth noting that if the truck 'a' only contained x=1, then he could move that 1 from 'a' to 'b' and still have time in his day to move 4 remaining things from 'c' onto 'b'. This is the crux of my problem. The worker can intelligently split his efforts when one of the 'buckets' is depleted. I've already determined that the worker is very predictable, he always wants to move 'x' from 'a' to 'b'. If 'a' is empty, he moved units from 'c' to 'b'. If there is no 'b', which in this example means there is no boat to load things onto, he moves 'a' to 'c'.


I'm trying to make this work in excel where I can, by date:
-set the day 1 values for 'a' and 'b'
-set the rate at which the worker can move inventory
-have the rows below fill out automatically.

[Tpiper1]

Perhaps this is something too programmed for a formula. Bump.

[MrShorty]

Did you try fiddling with shg's proposed solution in post #2? It seems to me that something along those lines is what you will end up using.

My own variation on his theme would be to separate out each value from the others. First calculate how many units you will take off of trucks (since that is the priority). Second, calculate how many units you put onto boats (since that is also the priority). Any difference between those two numbers will be what is put into or taken out of the stockpile. I think the correct combination of min() and max() functions should calculate the first and second quantities: =max(0,min(5,12)) -- a similar function for units to put on boats. Once you have calculated how much will move in/out of each "bin", what is available at the start of the next day should be a simple summation. For example, what is still available on trucks should be a simple =sum(startofdayontruck,-movedduringday). Other than using a few more "helper" cells, this is basically the kind of strategy shg proposed in post #2. I also have not looked at how to incorporate new trucks bringing in additional inventory or new boats bringing in additional "space to fill", though they should be simple additions to some of the sum statements. I also did not give any thought to "what if there is no inventory to move" situations. The sample spreadsheet should show you how I would structure the spreadsheet to think through some of these kind of questions.