Ordered Subtraction/Addition by Dates Until Reaching Given Value

A

alexb523

Board Regular
Joined
Dec 12, 2013
Messages
115
Hello & Sorry for the confusing title,

I am trying to do following in less steps (preferably one step). I will first explain what I need to be done, then how i am currently doing it.


I need to find out what week/date my "CurrentLevel" will hit (be equal to or below) my "LowestLevel" allowed based on two other tables which represent product coming in and demand for the product (product going out). These two tables (in tabular format) have dates associated with them and will effect when the "CurrentLevel" hits the "LowestLevel".

So, I could have 200 apples coming in but at separate times. 50 coming in next week, 50 coming in three weeks from now, etc. In addition, there is the reverse effect on demand. I could have 10 going this week, 30 going out the following week, etc.

I tried to represent these two tables below with "In" & "Out" columns.

wk range = 1 - 10 but i am working with actual dates that can go into next year etc.


Currently, pivot both to put into a matrix and then use another table and index-matches each in & out matrix to get the difference of what is happening that week for that product. Then I use another table that starts off at my currently level, use and index-match on the difference table and then, finally, I use that table and index-match when it is below the current stock value.

I am hoping there is just one grand formula I can use, or weed out some the step to get this info.

I tried my best to explain it here. If there is any confusion, please let me know.

Thanks!
alexb


Tables are below:


[TABLE="width: 231"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Product[/TD]
[TD]LowestLevel[/TD]
[TD]CurrentLevel[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]122[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]190[/TD]
[/TR]
[TR]
[TD]peachs[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]95[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Product[/TD]
[TD="width: 64"]Qty[/TD]
[TD="width: 64"]In (wk #)[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Product[/TD]
[TD="width: 64"]Qty[/TD]
[TD="width: 64"]Out (wk #)[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Your explanation is fine. Your solution is too convoluted. :)

Here's how I would solve it -- and how I tested it. It's easy to implement, easy to understand, easy to maintain, and lets Excel do all the heavy lifting.

Convert each of your ranges to a table (as Excel understands it). Note that Excel gives each table an unique name. In my case the names were:
Out table: Table1,
In table: Table2,
CurrentLevel (and lowestlevel) table: Table3, and
the new table I mention below: Table 15.

This is relevant because you will have to adjust my formulas depending on the table names in your worksheet.

For the In and the Out tables add a column, called Key, that combines the product and week.
Code: 
=[@Product]&CHAR(255)&[@[Out (wk '#)]]
and
Code: 
=[@Product]&CHAR(255)&[@[In (wk '#)]]

In the table with current and lowest levels, add a column (call it LowestZeroRel) with the formula
Code: 
=[@CurrentLevel]-[@LowestLevel]

Now, add a new table that has the product and week numbers of interest. I used the values from your 'Out' table (the headers for the 2 columns in this new table are Product and Wk).

Now, add 4 calculated columns: Key, Out, In, and Net. The formulas:
Code: 
=[@Product]&CHAR(255)&[@Wk]
Code: 
=INDEX(Table1[Qty],MATCH([@Key],Table1[Key],0))
Code: 
=IFERROR(INDEX(Table2[Qty],MATCH([@Key],Table2[Key],0)),0)
and
Code: 
=IF(OFFSET([@Product],-1,0)=[@Product],OFFSET([@Net],-1,0),INDEX(Table3[LowestZeroRel],MATCH([@Product],Table3[Product],0)))+[@In]-[@Out]

Now, go back to the CurrentLevel table and add a column . Name this column 'Wk Out'. The formula should be the *array* formula
Code: 
=MIN(IF((Table15[Net]<=0)*(Table15[Product]=[@Product]),Table15[Wk]))

To enter an array formula complete data entry with CTRL+SHIFT+ENTER rather than just the ENTER or TAB key. If done correctly, *Excel* will show the formula in curly brackets { and }

A value of zero in this column means you will never hit the 'Lowest Level' threshold. Any value > 0 is the week when you do hit the threshold.


alexb523 said:
Hello & Sorry for the confusing title,

I am trying to do following in less steps (preferably one step). I will first explain what I need to be done, then how i am currently doing it.


I need to find out what week/date my "CurrentLevel" will hit (be equal to or below) my "LowestLevel" allowed based on two other tables which represent product coming in and demand for the product (product going out). These two tables (in tabular format) have dates associated with them and will effect when the "CurrentLevel" hits the "LowestLevel".

So, I could have 200 apples coming in but at separate times. 50 coming in next week, 50 coming in three weeks from now, etc. In addition, there is the reverse effect on demand. I could have 10 going this week, 30 going out the following week, etc.

I tried to represent these two tables below with "In" & "Out" columns.

wk range = 1 - 10 but i am working with actual dates that can go into next year etc.


Currently, pivot both to put into a matrix and then use another table and index-matches each in & out matrix to get the difference of what is happening that week for that product. Then I use another table that starts off at my currently level, use and index-match on the difference table and then, finally, I use that table and index-match when it is below the current stock value.

I am hoping there is just one grand formula I can use, or weed out some the step to get this info.

I tried my best to explain it here. If there is any confusion, please let me know.

Thanks!
alexb


Tables are below:


[TABLE="width: 231"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Product[/TD]
[TD]LowestLevel[/TD]
[TD]CurrentLevel[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]122[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]190[/TD]
[/TR]
[TR]
[TD]peachs[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]95[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Product[/TD]
[TD="width: 64"]Qty[/TD]
[TD="width: 64"]In (wk #)[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Product[/TD]
[TD="width: 64"]Qty[/TD]
[TD="width: 64"]Out (wk #)[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Getting excel to return multiple results from one cell
Replies
6
Views
661
Sparhan
S
S
  • Question Question
Help searching for strings in 2D array and returning multiple results
Replies
8
Views
560
Fluff
Fluff
smide
  • Solved
Formula for percentile if data are in multiple columns
Replies
2
Views
82
hagia_sofia
hagia_sofia
S
  • Solved
Sum from a table if the key matches a variable array
Replies
3
Views
201
Fluff
Fluff
M
  • Question Question
Getting Cell Values Changing Frequently and Rearranging it in a Compact Manner
Replies
7
Views
385
maeyks
M

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top