Distribution of a set value against variables

[TheShoe56]

Sample Workbook.xlsx
	A	B	C	D
1		WEEK-01		EXPECTED VALUE
2	01-STORE	5
3	02-STORE	5
4	03-STORE	5
5	04-STORE	5
6
7
8	01-STORE_DELIVERY			5
9	02-STORE_DELIVERY			5
10	03-STORE_DELIVERY			2
11	04-STORE_DELIVERY			0
12
13
14	TOTAL PRODUCT	12
Sheet1

Hey everyone!

I'm working in Office 2016 and trying to build a formula that will take a set value and subtract that value against a set of requirements until the set value is zero. I attached a sample spreadsheet that better communicates what I am trying to do and the expected values.

Cells B2, B3, B4 and B5 contain the variable requirement. Cell B14 has the set value. I want to meet the needs of stores 01 first, 02 second, 03 third, and 04 last. My expected returns are in cells D8, D9, D10, and D11.

Any advice on improving what I am searching for, resources, etc., would be greatly appreciated!

Have a great day!

 

[KRice]

Welcome! This may need to be revised depending on a couple of things: I see WEEK-01 in the column heading. Will this be repeated for various weeks? Will your list of results appear in a table that is always the same order as the source information, or might it be different. Right now, I'm extracting the relevant portion of the text string to match with the source table's 1st column.

Book2
	A	B	C	D
1		WEEK-01		EXPECTED VALUE
2	01-STORE	5
3	02-STORE	5
4	03-STORE	5
5	04-STORE	5
6
7
8	01-STORE_DELIVERY			5
9	02-STORE_DELIVERY			5
10	03-STORE_DELIVERY			2
11	04-STORE_DELIVERY			0
12
13
14	TOTAL PRODUCT	12
TheShoe56

Cell Formulas
Range	Formula
D8:D11	D8	=MIN(MAX($B$14-SUM($D$7:D7)),INDEX($B$2:$B$5,MATCH(LEFT(A8,FIND("_",A8)-1),$A$2:$A$5,0)))

 

[TheShoe56]

Thank you very much for taking the time to give me feedback! The data will be repeated over 30 weeks, but each week will remain static and not influenced by the previous week's data.

As far as the store numbering, that data would also remain static.

One issue while implementing your solution that I should have considered is what would happen if I had excess product over store requirements.

I'm reviewing the MIN, and MAX functions you provided (thank you!) and working to build on your solution. I'll give a follow-up after some tinkering, someone else might find value in it as well!

Thank you again for the assistance!

 

[KRice]

Thanks for the feedback. The reason for my questions about the order of the store listings and whether multiple weeks might be involved becomes clearer when considering the following expanded example. Here we have a "TOTAL PRODUCT" that varies for each week and the order of the stores and weeks in the results table (prioritized allocations) is not the same as in the source table. This requires lookups for the Total Product value as well as matching to determine the appropriate row and column to use in the source table.

Book2
	A	B	C	D	E	F
1		WEEK-01	WEEK-02	WEEK-03	WEEK-04
2	01-STORE	5	3	1	2
3	02-STORE	5	2	2	2
4	03-STORE	5	4	3	2
5	04-STORE	1	5	4	2
6
7	TOTAL PRODUCT	12	13	4	5
8
9	Prioritized Allocations
10				WEEK-01	WEEK-02	WEEK-04
11	01-STORE_DELIVERY			5	3	2
12	02-STORE_DELIVERY			5	2	2
13	04-STORE_DELIVERY			1	5	1
14	03-STORE_DELIVERY			1	3	0
TheShoe56

Cell Formulas
Range	Formula
D11:F14	D11	=MIN(MAX(INDEX($B$7:$E$7,MATCH(D$10,$B$1:$E$1,0))-SUM(D$10:D10)),INDEX($B$2:$E$5,MATCH(LEFT($A11,FIND("_",$A11)-1),$A$2:$A$5,0),MATCH(D$10,$B$1:$E$1,0)))