How To Forecast Delivery Dates Based On Several Complex Conditions
Apr 10, 2012
I am trying to project the delivery dates of my shipments which is sent via courier to different destinations from my Distribution Centre.
The courier company delivers the parcels across Monday to Saturday(Sunday being the off day for the courier). There are different transit days for different locations which works in deriving the project dates from the shipping date. I am looking for a formula which can satisfy all the following conditions and project the delivery dates accordingly:-
Condition1:-If the projected delivery date falls on Sunday and there is no holiday on the very next day(Monday) then the projected delivery dates needs to be on Monday.
Condition2:- If the projected delivery date falls on Sunday and the very next Monday is a Holiday then the projected delivery dates needs to be on Tuesday.
Condition3:- If the projected delivery date is a Holiday and the holiday falls on any day between Monday to Friday then the delivery date will be the next working day post the holiday(between Mon-Sat).
Condition4:-If the projected delivery date is a Holiday and the holiday falls on Saturday then the delivery date will be next Monday.
Condition5A/5B/5C:-In case there are more than one holidays at a stretch(2/3 days of holidays at stretch) and if the projected delivery dates falls within the holidays then the projected delivery dates needs to be on the next working day(Between Monday to Saturday).
I have tried to write a formula but it is only satisfying the Condition1,Condition2 & Condition3. I am providing a sample data with desired results based on the above conditions. Any single formula which can derive the desired results.
Sheet1 ABCDEFGH1Destination LocationDispatch DateTransit DaysProjected
Delivery Dates(Desired Result)Remarks Courier Company Holiday List2A29-Mar32-Apr
Condition1:-Projected Delivery Date As Per Transit-Days Is Sunday(1st Apr) & There Is No Holiday On Next Monday(2nd Apr) & Hence REVISED Projected Delivery Date Needs To Be On 2nd Apr(Mon).
[Code] ....
View 3 Replies
ADVERTISEMENT
Apr 18, 2012
I am trying to project a dispatch date from my DC against the received customer orders.
The following are some important points for consideration of the projected dispatch date:-
1)Customer order is received 24X7(365 days via internet).
2)DC works between Monday-Saturday.
3)Courier company picks-up material from DC between Monday-Saturday.
4)Customer order processing CUT-OFF-TIME is 16PM(on any working day).
5)There are holidays for both DC & courier company on and above SUNDAY.
I am looking for a formula which can forecast the proposed pick-up date from the DC based on the following conditions:-
1)CUT-OFF-TIME:-If the customer order is received on any working day(except Sunday & Holiday) on or before the CUT-OFF-TIME of 16PM then the order will be dispatched on the same day or the dispatch will be made on the next working day.
2)SUNDAY/HOLOIDAY:-If the customer order is received on SUNDAY/designated HOLIDAYs(irrespective of any time) then the order will be dispatched on the next working day.
I am providing few probable scenarios with the desired result. Formula which can populate the desired result across D2:D11.
Sheet3 ABCDEFGH1Customer Order DateCustomer Order TimeOrder Processing CUT-OFF-TIME
Proposed Dispatch Date(Desired Result)Explanation For The Proposed Dispatch Date(Desired Result)
List Of Weekly Offs(Both DC & Courier)DC Holidays Courier Holidays
24-Apr14:2016:004-Apr4th April is a working day & order time is before the Cut-Off Time of 16PM and hence the dispatch will be done on 4th April.
[Code] ..........
View 1 Replies
View Related
Apr 4, 2014
I am trying to use sumif formula to add sales forecast based on three conditions but i also want to add the revenue for current month which i have but for the next one months as well as two months plus.. this will change based on the current month.. below is what I am using for the current month..
=IF($B$3=Reference_Data!E2,SUMIFS(Current!$K:$K,Current!$G:$G,"Yes",Current!$C:$C,$B$1,Current!$E:$E,$B$3),
IF($B$3=Reference_Data!E3,SUMIFS(Current!$K:$K,Current!$G:$G,"Yes",Current!$C:$C,$B$1,Current!$E:$E,$B$3),
IF($B$3=Reference_Data!E4,SUMIFS(Current!$K:$K,Current!$G:$G,"Yes",Current!$C:$C,$B$1,Current!$E:$E,$B$3),
[Code] ...........
View 2 Replies
View Related
Jul 23, 2013
I'm trying to capture a set of forecast dates in a spreadsheet. We have column 1 = forecast date, column 2 = actual date.
I also have at the top of the sheet (which I will hide when I get the forumula to work), today's date, today's date less 13 days and today's date - 27 days.
In column 3, I want a traffic light system whereby if the date in column 2 is equal to or less than (before) each of the 3 hidden dates it the cell colour will turn green, amber then red..
View 7 Replies
View Related
Feb 8, 2009
I want to create a chart than logs the progress of a project at work. Column 1 will have a series of target dates for certain tasks and column 2 will be filled in with the completed dates. I want automatically colour a cell (RC) based on the date given in the cell to the left (RC[-1]) giving a traffic light style warning of an upcoming target date and colour coding the cells when a task is completed on time (or not!). I can work out what the conditions are by creating 2 sets of 3 conditions (1 set if cell (RC) is empty and 1 set if cell (RC) has a date or "N/A" in it).
My conditions are:
1. Turn the cell background colour red (action overdue) if:
Formula Is:=IF(AND(RC[-1]"",RC="",RC[-1]
2. Turn the cell background colour orange (action required) if:
Formula Is: =IF(AND(RC[-1]"",RC="",RC[-1]
View 9 Replies
View Related
Dec 7, 2007
I import date information in text format and need to add to the date. My date info in in column "J" and in Column "K" I would desire to write a formula that allows me to to the following. My data looks like this in text: yyyymmdd
I desire to look in column "E" and I have 3 different variables. "J", "K", "L".
If column "E" has a j add 30 to the years; K add 35 years and L add 38 years.
for Example: 19630923 j = 19930923 k = 19980923 L = 20010923
View 5 Replies
View Related
Apr 4, 2014
I have a 52 week rolling forecast that I would like to have displayed in each calendar month that it corresponds to. I have come up with a solution where it does lump the data together into months but it is not a smooth lumping of the weeks as some weeks cross over from one month to the next. Is there a way to lump each week into its respective month. My current solution places in some months 4 weeks worth of data and in other months 5 weeks worth of data. Attached is the spreadsheet that I am using. The tab "Weekly Sales" is the 52 week data which has specified the exact dates on the calendar that that week represents. The "Monthly Sales" tab has the 12 week data which has specified the exact dates on the calendar that that month represents. I've tried SUMPRODUCT but that is giving me 4 weeks in some months and 5 weeks in others.
View 1 Replies
View Related
Oct 4, 2013
I am trying to find out how many projects might be active as of a certain date.
I have a tab in excel that contains project data. For each project there is a "Start Date" and there might be an "End Date"
My question is, how do I count all the rows where the start date is Less than a given date and the end date is after a given date? The wrinkle is... if the end date is empty then obviously it should be counted as well (assuming the start date is still before the given date).
Here are the approaches I've used thus far:
=SUMPRODUCT(1*( AND(OR(RawMetrics!P:P > A2, RawMetrics!P:P = ""), RawMetrics!$O:$O < A2 )))
=COUNTIFS(RawMetrics!P:P,">"&Sheet1!A2,RawMetrics!O:O,"
View 4 Replies
View Related
Mar 29, 2013
I need to find a way of populating a column of forecasts based upon previous sales amount and price. For instance if I have apples on special for $2 and previously sold 200 units on multiple occasions at this price but once off sold 1000 apples at special $1, but normally they are $3 selling on average 50. I would want to get a result of Forecast: 200, not 50 or anything else to far off
I've attached the sheet I currently use for work.
Dated tab: is my working sheet MerchTrend: Previous sales history, which is imported from POS system and unfortunately cells will change based upon sales
On the Dated Tab, price column includes multi buy prices (ie 2 for $3) but the Merch Trend refers to these as individual sales (ie 2 sales for $1.50) On the Merch trend, Price Type refers to promo style. (N for Normal Price, IA, S, R, IR, P are promotional)
promo sort example.xls
View 1 Replies
View Related
May 21, 2006
In the attached spreadsheet i have a budget amount, billed to date, %complete, %remaining and forecast figure. What i am trying to do is estimate the forecast spend vs the budget or billed to date and percent remaining. I am struggling with how to do this based on in some cases the budget is already overspent but the %complete is less than 100%. What i really want to do is create a forecast based on the billed to date or budget depending on which is greater and work out estimated spend based on whether the task is complete or there is still a % remaining.
View 2 Replies
View Related
Feb 25, 2014
I have a forecast monthly trial balance sheet and an Income Statement Analysis sheet. I am analyzing the Year to Date performance for Dec-2013. I need a formula that will match the income statement line i.e revenues, accounting expenses etc. and then sum horizontally from Jan-2013 to Dec 2013 ( if YTD month' Dec-2013' is greater than or equal to date range then sum horizontally the corresponding income statement lines up to the reporting month).
View 2 Replies
View Related
Aug 20, 2008
My spreadsheet is too large to attach so I'll describe it as best I can. I have names in column H, which are repeated irregularly. I have corresponding grade numbers (1-5) in column I, and I have the dates they were entered in column J. It looks something like this:
Column H Column I Column J
Name 1 4 8/10/08
Name 2 3.5 8/11/08
Name 1 5 8/11/08
Name 5 4 8/12/08
Etc....
I want to average the grades (column I) for a certain name (column H) within the last 30 days, or however many days I enter. I'm using Excel 2003 with the analysis add-in. I can use SUMIF to include any two columns, but how can I write an average grade formula using both the name and date conditions? Dan Auto Merged Post Until 24 Hrs Passes;Come to think of it, all I need to do is sum the data. I have a denominator in a different box that I can use to average it. So in effect, I'm trying to creat a SUMIFS function.
View 3 Replies
View Related
Feb 14, 2014
I need creating a formula that combines the following if statements in cell C107:
=IF(AND(B107
View 1 Replies
View Related
Mar 5, 2009
Dataset is 220K rows. A small subset follows.
Using an array calc with criterions Day = 1 and Hour = 1 summed on Elapsed Time returns 81 with an average of 11.57 for 7 events of which there are 5 distinct dates.
The function =SUM(IF(D2:D14<>"",1/COUNTIF(D2:D14,D2:D14))) returns the number of distinct dates in the dataset ie 7.
Is it possible to combine these two methods to create an average of elapsed time by day and hour using a denominator of distinct dates for only that combination of dimensions?
I have attempted to calculate an item in a pivot table using a different denominator without success.
day Hour Date Elapsed Time
1 0 5/03/2009 25
1 0 5/03/2009 24
1 1 5/03/2009 23
1 1 7/03/2009 11
1 0 7/03/2009 5
1 1 8/03/2009 2
1 1 8/03/2009 8
1 1 9/03/2009 4
1 1 9/03/2009 17
1 0 9/03/2009 22
1 1 10/03/2009 16
1 2 13/03/2009 12
2 1 14/03/2009 15
View 4 Replies
View Related
Jun 26, 2014
run a spreadsheet for Vendor Deliveries and got as far as promised delivery date and actual delivery date with a late or on time comment. Im trying to get a percentage from this data.the excel spreadsheet that i have uploaded.Delivery Schedule.xls
View 5 Replies
View Related
Mar 18, 2009
I am trying to get an average of delivery times.
On Monday's I have time's of:
View 6 Replies
View Related
Feb 20, 2014
I am about to apply for an entry-level job as a scheduler for a company that ships olive oil. But before I decide to actually do this I'd like to see what I'm getting myself into.
It is how Microsoft Excel is used to schedule shipments, monitor inbound shipments and keep track of inventory.
View 1 Replies
View Related
Feb 25, 2009
what i trying to learn is that im trying to get the delivery price calculate base on the delivery area(F10) and the total qty of the items(G10).
but i've tried with Vlookup, IF, lookup, Hlookup function and i still cant manage to get the right one to put the data in the H10
View 4 Replies
View Related
May 25, 2009
I want to calculate the number of times Lampard has taken and Inswing delivery, and how many times an attempt at goal ocurs from these deliveries.
The equation i'm using is =sum(if(a1:a100="Lampard",if(a3:a100="inswing",1,0)))
However, this is not calculating the correct number, and instead just equating 1.
View 9 Replies
View Related
Oct 27, 2009
I have 3 different costs for an item, 15 different items. The costs very depending on the delivery state. Is it possible to write a formula that will retrun the right cost for an item, depending on the delivery state?
if ap10 = "CA" then look up in range AH2 to AH18
if ap10 = "GA" then look up in range AI2 to AI18
if ap10 = "ME" then look up in range AI2 to AI18
if ap10 = "PR" then look up in range AI2 to AI18
if ap10 = any other state then look up in range AG2 to AG18
cell AP10 being the cell that the state is listed in
Cell U10 being the cell that item is in (a drop down menu)
This is the vlookup formula that will get one price.
=IF(ISNA(VLOOKUP($U10,TABLE!$AF$2:TABLE!$AI$18,2,FALSE)),"",VLOOKUP($U10,TABLE!$AF$2:TABLE!$AI$18,2, FALSE))
View 5 Replies
View Related
Dec 31, 2006
I have a very large Excel spreadsheet that is generated through an Oracle application at least once a month at work. I would like some code (either a worksheet function or a VBA) to check and report the following. If the frequency in column C is "Monthly" or "Weekly" just go to the next row. If the frequency in column C is "Annual", add 183 days to the date in column B; if it is "Semi-Annual", add 92 days to date in column B; if it is "Quarterly" , add 46 days to the date in column B; if the frequency in column C is "2-Year", add 365 days to the date in column B. Once the check has been made; I need the new total or date that was calculated checked against the date the report is being run (system clock date). If the date or total days is less than today, a new worksheet, titled "Late" needs to contain that row of data. If the date or total days is greater than today's date, just go on to the next row. I've attached an example worksheet.
View 9 Replies
View Related
Feb 12, 2013
I want formula which can automatically populate the amount based on the number of months starting from the date vehicle is received. For Eg.
Nissan patrol Rent is AED 5800/- per month & we deliver it on month Sept 2011 for 24 months then i want excel to automatically populate AED 5800/- for next 24 months starting from Sept 2011.
View 5 Replies
View Related
Mar 23, 2013
I am trying to build a sheet to track deliveries into the company I work for.
We book loads to come in at set times for production, I am wanting a sheet which I can enter the due time and the actual time of the the different companies and then produce a report in graph format to see which are the worst at late or early deliveries.
View 2 Replies
View Related
Mar 3, 2014
We have a weekly "On-Time" report that shows early, late, on time, or no data. Each week has a total percentage of early, late, on time, or no data deliveries. I now need a monthly total on a seperate worksheet, but for some reason I can not get the percentages to total correctly. I am attaching the spreadsheet.
View 3 Replies
View Related
Mar 19, 2009
I'm trying to forecast values for B48:B56. Charted the data and a polynomial 6th order fits best. Known values in B2:B47.
View 5 Replies
View Related
May 16, 2014
I have a list of african countries and their C02 emissions from 1990 to 2010. The question I'm asked is, who will be the top 5 emitters in the year 2020 given the current trends. I have done a lookup command and compiled a list of the top 5 emitters. My concern is though i do not know how to get the 2020 forecast of the top 5 emitters rather than the current datas.
View 4 Replies
View Related
Jan 4, 2007
I am using the Forecast formula to give me a value from a Linear trend. However I need to limit the resulting value that is displayed within a min and max value. Background: This is to allow me to calculate the amount of bonus someone will earn depending on the Percentages of their target they achieve. The min bonus is 25% when 80% of target is achieved and max bonus is 100% when 125% or greater of target is achieved.
So the Forecast formular works great using the existing values, but i need help limiting the results to between 25% and 100%. I was trying to use a sumif formula to copy the resulting cell but only if the value fell between the 25 and 100%. I've attached the spreadsheet as I think it will highlight what i'm trying to do better than I can explain it.
View 2 Replies
View Related
Mar 3, 2007
I try to predict some macro economic statistics but any attempt till now didn't make sense. the attached file. Note: when i used the FORECAST function the predicted values showed an unlogical drop while there seems to be a positive trend.
View 4 Replies
View Related
Jun 18, 2008
I have inherited a series of data relating to a change in a specification over a period of time and a number of cycles.
See attached.
There is already a chart which shows the data and has an exponential line.
I want to find the value of Cycles where the Average Flash exponential from the chart line is 0.131.
FYI this is to plot deterioration in a piece of tooling, 0.131 being the accepted warning level. If you feel there is a better statistical model to use for that application then I'm all ears!
View 9 Replies
View Related
Dec 1, 2007
I want to have SUMIF and IF functions to be combined. i tried using the formula SUMIF( Timesheet!D2:D55,B2,Timesheet!F2:F55) but i want to edit the range everytime.
Can i have formula like if name = Ant merce and prj = Implementation support, then the sum will be 15 for the sumrange of Timesheet!F:F (entire F column)
View 4 Replies
View Related
