# Age of Queue each day based on 1 assumption

#### Michaelm1122

##### New Member
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

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

Then use a table like this to enter your assumptions about daily production and calculate the age of the queue based on those assumptions.

Last edited:

#### Michaelm1122

##### New Member
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
Michael, That's my bad 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)

#### Michaelm1122

##### New Member
Can you give me a brief explanation of how the formulas work to accomplish this task? Thanks

#### Jerry Sullivan

##### MrExcel MVP
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.

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