Ordered Subtraction/Addition by Dates Until Reaching Given Value

[alexb523]

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:

Product	LowestLevel	CurrentLevel
apples	50	122
oranges	150	190
peachs	25	56
watermelon	32	95

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Product	Qty	In (wk #)
apples	50	5
peaches	10	10
apples	50	1
peaches	10	3
peaches	10	9
peaches	10	2
oranges	100	5
peaches	10	7
oranges	100	4
watermelon	75	9
watermelon	75	5
watermelon	75	3
apples	50	7

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

Product	Qty	Out (wk #)
apples	10	1
apples	20	2
apples	35	3
apples	27	4
apples	23	5
apples	8	6
apples	45	7
apples	7	8
apples	18	9
apples	12	10
peaches	5	1
peaches	12	2
peaches	10	3
peaches	6	4
peaches	12	5
peaches	12	6
peaches	14	7
peaches	6	8
peaches	7	9
peaches	12	10
oranges	25	1
oranges	24	2
oranges	30	3
oranges	32	4
oranges	15	5
oranges	30	6
oranges	35	7
oranges	15	8
oranges	25	9
oranges	26	10
watermelon	35	1
watermelon	32	2
watermelon	33	3
watermelon	27	4
watermelon	35	5
watermelon	25	6
watermelon	35	7
watermelon	28	8
watermelon	28	9
watermelon	23	10

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

 

[tusharm]

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.

=[@Product]&CHAR(255)&[@[Out (wk '#)]]

and

=[@Product]&CHAR(255)&[@[In (wk '#)]]

In the table with current and lowest levels, add a column (call it LowestZeroRel) with the formula

=[@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:

=[@Product]&CHAR(255)&[@Wk]

=INDEX(Table1[Qty],MATCH([@Key],Table1[Key],0))

=IFERROR(INDEX(Table2[Qty],MATCH([@Key],Table2[Key],0)),0)

and

=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

=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.

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:

Product	LowestLevel	CurrentLevel
apples	50	122
oranges	150	190
peachs	25	56
watermelon	32	95

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Product	Qty	In (wk #)
apples	50	5
peaches	10	10
apples	50	1
peaches	10	3
peaches	10	9
peaches	10	2
oranges	100	5
peaches	10	7
oranges	100	4
watermelon	75	9
watermelon	75	5
watermelon	75	3
apples	50	7

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

Product	Qty	Out (wk #)
apples	10	1
apples	20	2
apples	35	3
apples	27	4
apples	23	5
apples	8	6
apples	45	7
apples	7	8
apples	18	9
apples	12	10
peaches	5	1
peaches	12	2
peaches	10	3
peaches	6	4
peaches	12	5
peaches	12	6
peaches	14	7
peaches	6	8
peaches	7	9
peaches	12	10
oranges	25	1
oranges	24	2
oranges	30	3
oranges	32	4
oranges	15	5
oranges	30	6
oranges	35	7
oranges	15	8
oranges	25	9
oranges	26	10
watermelon	35	1
watermelon	32	2
watermelon	33	3
watermelon	27	4
watermelon	35	5
watermelon	25	6
watermelon	35	7
watermelon	28	8
watermelon	28	9
watermelon	23	10

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