Age of Queue each day based on 1 assumption

Michaelm1122

New Member
Joined
Aug 12, 2014
Messages
12
Sorry I thought you had enough info is there anyway I can send you my worksheet? I Can't figure out how to attach on this website
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Michael, Thanks for sending a copy of your file. Here's a screen shot of part of your worksheet for the benefit of others that find this thread...

<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;">Monday - January 11th</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">Age</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">83</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">82</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">81</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">78</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">77</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">76</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">75</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">Row Labels</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Queue</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">64</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">258</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">279</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">237</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">563</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">536</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">304</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;text-align: right;;">Production</td><td style="font-weight: bold;text-align: center;;">275</td><td style="font-weight: bold;text-align: center;color: #FF0000;;"></td><td style="font-weight: bold;text-align: center;color: #FF0000;;"></td><td style="font-weight: bold;text-align: center;color: #FF0000;;"></td><td style="font-weight: bold;text-align: center;;">(day 82)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: center;color: #FF0000;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="font-weight: bold;border-bottom: 1px solid black;;">Tueday - January 12th</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">Age</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">83</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">82</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">79</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">78</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">77</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">Row Labels</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Queue</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">47</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">279</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">237</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">563</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">536</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="font-weight: bold;text-align: right;;">Production</td><td style="font-weight: bold;text-align: center;;">275</td><td style="font-weight: bold;text-align: center;color: #FF0000;;"></td><td style="font-weight: bold;text-align: center;color: #FF0000;;"></td><td style="font-weight: bold;text-align: center;color: #FF0000;;"></td><td style="font-weight: bold;text-align: center;;">(day 82)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

Your worksheet has about 30 of these sets of data showing the forecasted queue for each day.

If your objective is to create a chart that shows the forecasted age of the queue for each of the next 30 days, it would seem that the minimum inputs needed are:

1. The make up of the queue today (Rows 5:8)
2. The daily production or items to be cleared each day (B10, B17...)

Note that since your daily production is not constant (you have variations from 275 farther down the sheet), this calls for a slightly different approach than if you assumed a constant 50 items/ day.

Do you have control over the way this data is organized, and would you be open to changing that if it made the process faster and easier to maintain?

Are the other 29 sets of data needed for your use for other purposes, or are they only being used as a means for you to make the 30 day forecast?
 
Last edited:

Michaelm1122

New Member
Joined
Aug 12, 2014
Messages
12
Jerry yes I have control over the way the data is organized. The age of the queue is essentially the oldest item in the queue minus todays date. The method that you see here is the inefficient way you are helping me solve for. The production per day is set a week at a time based on the amount of resources available to work the queue in a given week that is why you see a change in the production number. You are correct to assume we are only interested in the tail of the data each day essentially what day are we working the queue at each day in the future. The forecast is updated once a week with the current inventory numbers for each day, and the updated assumption of what will get closed each week. The challenge is that on any given day we may be able to get through that days worth of inventory and move to the next day and possible three day ahead, or if they volume is high it may take two or three days to work through that days batch of inventory.
 

Michaelm1122

New Member
Joined
Aug 12, 2014
Messages
12
Ideally it would be great if I could take this all the way in to the future and use as sort of a goal seek to see what our production needs to be to drive down the age of the queue. So the inventory fluctuates each day and we have new inventorycome in every day. However the part we have control over is the production, if I add resources, gain efficiencies, the volume drops and can drive the age of the queue down. So for example if I can get production to 1000 a day I can drive the age down. This is the part that is forecasted potentially making all inventory dates in the future relevant
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Michael, Here's a simpler process for you to consider.

List inventory by inventory create date as shown here. For dates, don't use text strings like "Monday - January 11th", but rather: 1/11/2016. This allows you use the dates to make calculation. You can use number formatting to customize the way the date values are displayed.

<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-bottom: 1px solid black;;">As of Start of Day</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Monday - January 11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10/20</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10/21</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10/22</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10/25</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10/26</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10/27</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Queue</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">64</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">258</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">279</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">237</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">563</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">536</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Queue Running Total</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">64</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">322</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">601</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">838</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1401</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1937</td></tr></tbody></table><p style="width:8.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B4</th><td style="text-align:left">=N(<font color="Blue">A4</font>)+B3</td></tr></tbody></table></td></tr></table><br />
Then use a table like this to enter your assumptions about daily production and calculate the age of the queue based on those assumptions.

<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;background-color: #DCE6F1;;"></td><td style="text-align: right;background-color: #DCE6F1;;">Forecast</td><td style="text-align: right;background-color: #DCE6F1;;">Running</td><td style="text-align: right;background-color: #DCE6F1;;">Match</td><td style="text-align: right;background-color: #DCE6F1;;">Queue</td><td style="text-align: right;background-color: #DCE6F1;;">Age of</td><td style="text-align: right;background-color: #DCE6F1;;">Remaining</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;background-color: #DCE6F1;;">Production Day</td><td style="text-align: right;background-color: #DCE6F1;;">Production</td><td style="text-align: right;background-color: #DCE6F1;;">Production</td><td style="text-align: right;background-color: #DCE6F1;;">Column</td><td style="text-align: right;background-color: #DCE6F1;;">Date</td><td style="text-align: right;background-color: #DCE6F1;;">Queue</td><td style="text-align: right;background-color: #DCE6F1;;">Items</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">Monday - January 11</td><td style="text-align: right;;">275</td><td style="text-align: right;;">275</td><td style="text-align: right;;">1</td><td style="text-align: right;;">10/21</td><td style="text-align: right;;">82</td><td style="text-align: right;;">47</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">Tuesday - January 12</td><td style="text-align: right;;">275</td><td style="text-align: right;;">550</td><td style="text-align: right;;">2</td><td style="text-align: right;;">10/22</td><td style="text-align: right;;">82</td><td style="text-align: right;;">51</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">Wednesday - January 13</td><td style="text-align: right;;">275</td><td style="text-align: right;;">825</td><td style="text-align: right;;">3</td><td style="text-align: right;;">10/25</td><td style="text-align: right;;">80</td><td style="text-align: right;;">13</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">Thursday - January 14</td><td style="text-align: right;;">275</td><td style="text-align: right;;">1,100</td><td style="text-align: right;;">4</td><td style="text-align: right;;">10/26</td><td style="text-align: right;;">80</td><td style="text-align: right;;">301</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">Friday - January 15</td><td style="text-align: right;;">275</td><td style="text-align: right;;">1,375</td><td style="text-align: right;;">4</td><td style="text-align: right;;">10/26</td><td style="text-align: right;;">81</td><td style="text-align: right;;">26</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">Monday - January 18</td><td style="text-align: right;;">100</td><td style="text-align: right;;">1,475</td><td style="text-align: right;;">5</td><td style="text-align: right;;">10/27</td><td style="text-align: right;;">83</td><td style="text-align: right;;">462</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">Tuesday - January 19</td><td style="text-align: right;;">300</td><td style="text-align: right;;">1,775</td><td style="text-align: right;;">5</td><td style="text-align: right;;">10/27</td><td style="text-align: right;;">84</td><td style="text-align: right;;">162</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">Wednesday - January 20</td><td style="text-align: right;;">300</td><td style="text-align: right;;">2,075</td><td style="text-align: right;;">6</td><td style="text-align: right;;">10/28</td><td style="text-align: right;;">84</td><td style="text-align: right;;">166</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">Thursday - January 21</td><td style="text-align: right;;">300</td><td style="text-align: right;;">2,375</td><td style="text-align: right;;">7</td><td style="text-align: right;;">10/29</td><td style="text-align: right;;">84</td><td style="text-align: right;;">118</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">Friday - January 22</td><td style="text-align: right;;">300</td><td style="text-align: right;;">2,675</td><td style="text-align: right;;">8</td><td style="text-align: right;;">10/31</td><td style="text-align: right;;">83</td><td style="text-align: right;;">118</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">Monday - January 25</td><td style="text-align: right;;">300</td><td style="text-align: right;;">2,975</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11/4</td><td style="text-align: right;;">82</td><td style="text-align: right;;">63</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">Tuesday - January 26</td><td style="text-align: right;;">300</td><td style="text-align: right;;">3,275</td><td style="text-align: right;;">12</td><td style="text-align: right;;">11/5</td><td style="text-align: right;;">82</td><td style="text-align: right;;">106</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">Wednesday - January 27</td><td style="text-align: right;;">300</td><td style="text-align: right;;">3,575</td><td style="text-align: right;;">13</td><td style="text-align: right;;">11/8</td><td style="text-align: right;;">80</td><td style="text-align: right;;">78</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">Thursday - January 28</td><td style="text-align: right;;">300</td><td style="text-align: right;;">3,875</td><td style="text-align: right;;">14</td><td style="text-align: right;;">11/9</td><td style="text-align: right;;">80</td><td style="text-align: right;;">393</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">Friday - January 29</td><td style="text-align: right;;">300</td><td style="text-align: right;;">4,175</td><td style="text-align: right;;">14</td><td style="text-align: right;;">11/9</td><td style="text-align: right;;">81</td><td style="text-align: right;;">93</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">Monday - February 01</td><td style="text-align: right;;">325</td><td style="text-align: right;;">4,500</td><td style="text-align: right;;">15</td><td style="text-align: right;;">11/10</td><td style="text-align: right;;">83</td><td style="text-align: right;;">412</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">Tuesday - February 02</td><td style="text-align: right;;">325</td><td style="text-align: right;;">4,825</td><td style="text-align: right;;">15</td><td style="text-align: right;;">11/10</td><td style="text-align: right;;">84</td><td style="text-align: right;;">87</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">Wednesday - February 03</td><td style="text-align: right;;">325</td><td style="text-align: right;;">5,150</td><td style="text-align: right;;">16</td><td style="text-align: right;;">11/11</td><td style="text-align: right;;">84</td><td style="text-align: right;;">113</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">Thursday - February 04</td><td style="text-align: right;;">325</td><td style="text-align: right;;">5,475</td><td style="text-align: right;;">17</td><td style="text-align: right;;">11/12</td><td style="text-align: right;;">84</td><td style="text-align: right;;">70</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">Friday - February 05</td><td style="text-align: right;;">325</td><td style="text-align: right;;">5,800</td><td style="text-align: right;;">18</td><td style="text-align: right;;">11/15</td><td style="text-align: right;;">82</td><td style="text-align: right;;">74</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">Monday - February 08</td><td style="text-align: right;;">350</td><td style="text-align: right;;">6,150</td><td style="text-align: right;;">19</td><td style="text-align: right;;">11/16</td><td style="text-align: right;;">84</td><td style="text-align: right;;">531</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">Tuesday - February 09</td><td style="text-align: right;;">350</td><td style="text-align: right;;">6,500</td><td style="text-align: right;;">19</td><td style="text-align: right;;">11/16</td><td style="text-align: right;;">85</td><td style="text-align: right;;">181</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;">Wednesday - February 10</td><td style="text-align: right;;">350</td><td style="text-align: right;;">6,850</td><td style="text-align: right;;">20</td><td style="text-align: right;;">11/17</td><td style="text-align: right;;">85</td><td style="text-align: right;;">666</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">Thursday - February 11</td><td style="text-align: right;;">350</td><td style="text-align: right;;">7,200</td><td style="text-align: right;;">20</td><td style="text-align: right;;">11/17</td><td style="text-align: right;;">86</td><td style="text-align: right;;">316</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;">Friday - February 12</td><td style="text-align: right;;">350</td><td style="text-align: right;;">7,550</td><td style="text-align: right;;">21</td><td style="text-align: right;;">11/18</td><td style="text-align: right;;">86</td><td style="text-align: right;;">304</td></tr></tbody></table><p style="width:8.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A9</th><td style="text-align:left">=A2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A10</th><td style="text-align:left">=A9+IF(<font color="Blue">WEEKDAY(<font color="Red">A9</font>)=6,3,1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C9</th><td style="text-align:left">=B9+N(<font color="Blue">C8</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D9</th><td style="text-align:left">=MATCH(<font color="Blue">C9,$B$4:$BN$4,1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E9</th><td style="text-align:left">=INDEX(<font color="Blue">$B$2:$BN$2,1,D9+1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F9</th><td style="text-align:left">=A9-E9</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G9</th><td style="text-align:left">=INDEX(<font color="Blue">$B$4:$BN$4,1,D9+1</font>)-C9</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Michaelm1122

New Member
Joined
Aug 12, 2014
Messages
12
It is working pretty well, for some reason however it is giving me #N/A errors just on the D9,E9,F9, and G9? All of the rest of the rows have data filled in.
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Michael, That's my bad :p Match will a match_type parameter "less than" will error if the lookup value is less than the first value in the lookup.

I hadn't anticipated the scenario of the first day production not clearing the first day of inventory.

Just change the formula in D9 this and copy down:

=IFERROR(MATCH(C9,$B$4:$BN$4,1),0)
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Can you give me a brief explanation of how the formulas work to accomplish this task? Thanks
Here's an explanation based on the screen shot in Post #15 (with corrected formula in Post #17):

Code:
[B]B4: =N(A4)+B3  [/B]
Calculates running total of queue items. 
The N() function avoids an error if non-numeric value is in referenced cell.

[B]A9:=A2 [/B]
Carries down start date to ensure production table starts on same date as the inventory in Rows 2:4

[B]A10: =A9+IF(WEEKDAY(A9)=6,3,1) [/B]
Lists dates and skips weekends. If previous date was a Friday, returns 
next Monday's date, otherwise returns the next date.

[B]C9: =B9+N(C8) [/B]
Calculates running total of forecasted production. The N() function avoids an error 
if a non-numeric value is in referenced cell.

[B]D9: =IFERROR(MATCH(C9,$B$4:$BN$4,1),0) [/B]
Finds the largest value in the Queue Running Total (Row 4) that is less than or equal 
to the lookup value (the Running Production Total for that date). Returns the 
Column number for that matched item. The IFERROR part handles the scenario 
that no values in the Queue Running Total are less than the lookup value.

[B]E9: =INDEX($B$2:$BN$2,1,D9+1) [/B]
Returns Queue Date by looking in the Inventory Dates (Row 2). The Queue Date 
will be one Column to the right of the Column found by the Match formula in D9.

[B]F9: =A9-E9 [/B]
Calculates Age of Queue by subtracting Queue Date (10/21/2015) 
from Production Date (1/11/2016)

[B]G9 =INDEX($B$4:$BN$4,1,D9+1)-C9 [/B]
Calculates how many items will be remaining from the Queue Date's inventory. 
Subtracts the Running Production Total from the Queue Running Total for 
the Queue Date and returns the difference.
Just ask if you'd like me to elaborate further on any of these.
 

Forum statistics

Threads
1,089,491
Messages
5,408,592
Members
403,216
Latest member
Boba Fetts

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top