Spreading Value of Column D Over In Column C Based On A.

H

hasnu01

New Member
Joined
Apr 12, 2024
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I want to Enter the total available qty in Stock for 4/23 and 4/24 in Cell D2 and it automatically distribute the qty over accounts equally and rounded. Let say the total qty I have to ship for 4/23 and 4/24 is 500 and I have 400 available I still want it to distribute as ordered and keep difference on last account if the order met as ordered say YES in column E if not (as in last acct) say NO in Column E. I will only have to enter value in D2 for 4/23 and 4/24 and in D13 for 4/25 and 4/26. please help with formula. Thanks
exc.xlsx
ABCDE
1Bread DayCase Quantity Ordered Qty to shipENTER QTY in StockCOMPLETE ORDER YES?NO
2
304/23152.5
404/23145
504/23100
604/23125
7
804/24150
904/24117.5
1004/24145
1104/24165
1204/24150
13
1404/25212.5
1504/2556.25
1604/2575
1704/2566.25
1804/25112.5
1904/25187.5
2004/25106.25
21
2204/26112.5
2304/26162.5
2404/26112.5
2504/26200
Sheet1
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Welcome to the MrExcel forum!

It would help enormously if you could manually calculate the expected results for this example. I made a few assumptions. I had to add a column for the end date, because otherwise I didn't know where to end the range where the quantity would be spread. I had a version where the amount and date were in the same cell, but it really looked bad. So, starting with this layout,

Book1
ABCDEF
1Bread DayCase Quantity Ordered Qty to shipENTER QTY in StockENTER End DateCOMPLETE ORDER YES?NO
2  
34/23/2024152.5  
44/23/2024145  
54/23/2024100  
64/23/2024125  
7  
84/24/2024150  
94/24/2024117.5  
104/24/2024145  
114/24/2024165  
124/24/2024150  
13  
144/25/2024212.5  
154/25/202456.25  
164/25/202475  
174/25/202466.25  
184/25/2024112.5  
194/25/2024187.5  
204/25/2024106.25  
21  
224/26/2024112.5  
234/26/2024162.5  
244/26/2024112.5  
254/26/2024200  
Sheet7
Cell Formulas
RangeFormula
C2:C25C2=IF(A2="","",LET(x,XMATCH(TRUE,D$2:D2<>"",0,-1),amt,INDEX(D$2:D2,x)+0,dat,INDEX(E$2:E2,x),IFERROR(IF(A2<=dat,ROUNDUP(MEDIAN(0,B2,amt-SUM(INDEX(C$1:C1,x):C1)),0),""),"")))
F2:F25F2=IF(D2="","",IF(SUMIFS(B3:B100,A3:A100,">="&A3,A3:A100,"<="&E2)<=D2,"Yes","No"))


I added the new column E with the date, and added formulas to columns C and F. When you put in a quantity and date, it becomes:

Bread DayCase Quantity Ordered Qty to shipENTER QTY in StockENTER End DateCOMPLETE ORDER YES?NO
7504/24/2024No
4/23/2024152.5153
4/23/2024145145
4/23/2024100100
4/23/2024125125
4/24/2024150150
4/24/2024117.577
4/24/20241450
4/24/20241650
4/24/20241500
4/25/2024212.5
4/25/202456.25
4/25/202475
4/25/202466.25
4/25/2024112.5
4/25/2024187.5
4/25/2024106.25
4/26/2024112.5
4/26/2024162.5
4/26/2024112.5
4/26/2024200


And adding another quantity/date:

Bread DayCase Quantity Ordered Qty to shipENTER QTY in StockENTER End DateCOMPLETE ORDER YES?NO
7504/24/2024No
4/23/2024152.5153
4/23/2024145145
4/23/2024100100
4/23/2024125125
4/24/2024150150
4/24/2024117.577
4/24/20241450
4/24/20241650
4/24/20241500
15004/26/2024Yes
4/25/2024212.5213
4/25/202456.2557
4/25/20247575
4/25/202466.2567
4/25/2024112.5113
4/25/2024187.5188
4/25/2024106.25107
4/26/2024112.5113
4/26/2024162.5163
4/26/2024112.5113
4/26/2024200200


Let us know if this is on the right track.
 
Upvote 1
Solution
Thanks Eric, I really appreciate your help.
This is exactly what and how I wanted it. But I don't why its giving not the right numbers on last cells of every end date for example you can see on Cell B25 my required Qty is 1403.75 and it rounded up maybe a little more so I put the QTY in stock as a little over but it still give wrong number on C25. The only time it gives the right number is when I put a very large number in QTY in stock, But practically we dont usually have everything as ordered.
exc.xlsx
ABCDEF
1Bread DayCase Quantity Ordered Qty to shipENTER QTY in StockENTER End DateCOMPLETE ORDER YES?NO
2 15004/24/2024Yes
34/23/2024152.5153 
44/23/2024145145 
54/23/2024100100 
64/23/2024125125 
7  
84/24/2024150150 
94/24/2024117.5118 
104/24/2024145145 
114/24/2024165165 
124/24/2024150150 
1314104/26/2024Yes
144/25/2024212.5213 
154/25/202456.2557 
164/25/20247575 
174/25/202466.2567 
184/25/2024112.5113 
194/25/2024187.5188 
204/25/2024106.25107 
21  
224/26/2024112.5113 
234/26/2024162.5163 
244/26/2024112.5113 
254/26/202420051 
26TOTAL1403.751260
Sheet2
Cell Formulas
RangeFormula
C2:C12,C14:C25C2=IF(A2="","",LET(x,XMATCH(TRUE,D$2:D2<>"",0,-1),amt,INDEX(D$2:D2,x)+0,dat,INDEX(E$2:E2,x),IFERROR(IF(A2<=dat,ROUNDUP(MEDIAN(0,B2,amt-SUM(INDEX(C$1:C1,x):C1)),0),""),"")))
F2:F25F2=IF(D2="","",IF(SUMIFS(B3:B100,A3:A100,">="&A3,A3:A100,"<="&E2)<=D2,"Yes","No"))
C26C26=SUM(C14:C25)
 
Upvote 0
Are you saying that you want the last date in each range to take all of the remaining stock? Try:

Book1
ABCDEF
1Bread DayCase Quantity Ordered Qty to shipENTER QTY in StockENTER End DateCOMPLETE ORDER YES?NO
2 15004/24/2024Yes
34/23/2024152.5153 
44/23/2024145145 
54/23/2024100100 
64/23/2024125125 
7  
84/24/2024150150 
94/24/2024117.5118 
104/24/2024145145 
114/24/2024165165 
124/24/2024150399 
13 14104/26/2024Yes
144/25/2024212.5213 
154/25/202456.2557 
164/25/20247575 
174/25/202466.2567 
184/25/2024112.5113 
194/25/2024187.5188 
204/25/2024106.25107 
21  
224/26/2024112.5113 
234/26/2024162.5163 
244/26/2024112.5113 
254/26/2024200201 
26TOTAL1403.751410
Sheet8
Cell Formulas
RangeFormula
C2:C25C2=IF(A2="","",LET(x,XMATCH(TRUE,D$2:D2<>"",0,-1),amt,INDEX(D$2:D2,x)+0,dat,INDEX(E$2:E2,x),rem,amt-SUM(INDEX(C$2:C2,x):C1),IFERROR(IFS(COUNTIF(A3:A4,"<="&dat)=0,rem,A2<=dat,ROUNDUP(MEDIAN(0,B2,rem),0)),"")))
F2:F25F2=IF(D2="","",IF(SUMIFS(B3:B100,A3:A100,">="&A3,A3:A100,"<="&E2)<=D2,"Yes","No"))
B26:C26B26=SUM(B14:B25)
 
Upvote 1
Thanks again Eric.
I think that's not what I meant.
Please review the RED COLORED CELLS in Below Sheet.
I am trying to distribute a QTY of "1420" over date range of 04/25 and 04/26 and you will notice that its distributing only 1270.
I noticed the formulas worked fine until 2nd last Row (24) and not giving accurate number on last row (25). its giving only 61 against 200 QTY.
exc.xlsx
ABCDEF
1Bread DayCase Quantity Ordered Qty to shipENTER QTY in StockENTER End DateCOMPLETE ORDER YES?NO
2 15004/24/2024Yes
34/23/2024152.5153 
44/23/2024145145 
54/23/2024100100 
64/23/2024125125 
7  
84/24/2024150150 
94/24/2024117.5118 
104/24/2024145145 
114/24/2024165165 
124/24/2024150150 
1314204/26/2024Yes
144/25/2024212.5213 
154/25/202456.2557 
164/25/20247575 
174/25/202466.2567 
184/25/2024112.5113 
194/25/2024187.5188 
204/25/2024106.25107 
21  
224/26/2024112.5113 
234/26/2024162.5163 
244/26/2024112.5113 
254/26/202420061 
26TOTAL1403.751270
Sheet2
Cell Formulas
RangeFormula
C2:C12,C14:C25C2=IF(A2="","",LET(x,XMATCH(TRUE,D$2:D2<>"",0,-1),amt,INDEX(D$2:D2,x)+0,dat,INDEX(E$2:E2,x),IFERROR(IF(A2<=dat,ROUNDUP(MEDIAN(0,B2,amt-SUM(INDEX(C$1:C1,x):C1)),0),""),"")))
F2:F25F2=IF(D2="","",IF(SUMIFS(B3:B100,A3:A100,">="&A3,A3:A100,"<="&E2)<=D2,"Yes","No"))
C26C26=SUM(C14:C25)
 
Upvote 0
Are you saying you want the whole In Stock quantity spread out proportionately among the days? If so, try:

Book1
ABCDEF
1Bread DayCase Quantity Ordered Qty to shipENTER QTY in StockENTER End DateCOMPLETE ORDER YES?NO
2 15004/24/2024Yes
34/23/2024152.5183 
44/23/2024145174 
54/23/2024100120 
64/23/2024125150 
7  
84/24/2024150180 
94/24/2024117.5141 
104/24/2024145174 
114/24/2024165198 
124/24/2024150180 
13 14104/26/2024Yes
144/25/2024212.5214 
154/25/202456.2557 
164/25/20247576 
174/25/202466.2567 
184/25/2024112.5113 
194/25/2024187.5188 
204/25/2024106.25107 
21  
224/26/2024112.5113 
234/26/2024162.5163 
244/26/2024112.5113 
254/26/2024200199 
26TOTAL1403.751410
Sheet8
Cell Formulas
RangeFormula
C2:C25C2=IF(A2="","",LET(x,XMATCH(TRUE,D$2:D2<>"",0,-1),amt,INDEX(D$2:D2,x)+0,dat,INDEX(E$2:E2,x),rem,amt-SUM(INDEX(C$2:C2,x):C1),qo,SUMIFS(B2:B99,A2:A99,"<="&dat),IFERROR(IFS(COUNTIF(A3:A4,"<="&dat)=0,rem,A2<=dat,ROUNDUP(B2/qo*rem,0)),"")))
F2:F25F2=IF(D2="","",IF(SUMIFS(B3:B100,A3:A100,">="&A3,A3:A100,"<="&E2)<=D2,"Yes","No"))
B26:C26B26=SUM(B14:B25)


Notice that on the 4/23 - 4/24 range, the amounts are higher than the ordered quantity, while the 4/25 - 2/26 range, they're pretty close.

If this is not what you're looking for, please manually calculate the values you want, and explain how you got them. The saying "A picture is worth a thousand words" definitely applies here.
 
Upvote 1
You must log in or register to reply here.

Similar threads

J
  • Question
Total Difference between Two Columns
Replies
1
Views
254
GuzmanServices
G
Z
  • Question
Massive Nested IF Statement Help Needed
Replies
1
Views
132
Zutalores
Z
J
  • Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
1K
felixstraube
F
J
  • Solved
How to aggregate data based on multiple criteria?
Replies
8
Views
551
Jurez
J
M
  • Solved
VBA Copy & Paste Values onto itself based on Criteria
Replies
4
Views
240
mst3kr
M

Forum statistics

Threads
1,215,196
Messages
6,123,575
Members
449,108
Latest member
rache47

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