So I'm trying to forecast the number of passengers for an airport based on their past 7 years performance. I used "TREND" to forecast the passenger number from 2008 through to 2012 with a little problem and the numbers seems reasonable. However, When I do a scatter diagram and add a trend line, my R squared value is VERY low something around 0.03. I have a limited statistical knowledge but I assume this value should be above 0.90 in order for a good fit with trend line.
I am forecasting some numbers and I wanted to see which function would be best suited for my task. I used the TREND function and then the FORECAST, I was expecting the same results but appeared to have slight differences.
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.
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.
When using the Trend function, I can get the value for individual future periods. I then can get a total for the year by summing each individual future period.
What I don't know how to do is sum them all using one formula. For instance, If I know the values of periods 1-5, how would I write a formula that would sum the future values (in one cell) of periods 6-12 -- without having to sum each future period individually?
I am trying to use VBA to find a value on a trendline. I have been unable to call the TREND function from Excel, and I can't see what I'm doing wrong. I'm using the following line of point25 = WorksheetFunction.TREND( Range("d2:d25"), Range("c2:c25"), 25)
I keep getting "Runtime error 1004 - Unable to get the Trend property of the WorksheetFunction class".
I currently have in my equation the trend function to linear extrapolate the date a line would cross either my upper or lower limits (100.1 and 19.9) between 2 data points. However if the 2 data points span both the upper and lower limits (as per the 2 attached examples) then unfortunately the trend function is only able to return the 1 date (where I've specified the x value). Is there any way for the TREND function to trend the 2 values? Do I simply add the 2nd x value to the new x's?
I have attached both the data and their graphs to visually explain my problem : EF Workbook.xlsx
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.
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.
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!
I would like to know a way to sum the future months dollars only once the month has passed to consider that amount only in my forecast. For eg. If I have a Vendor A contract from Jan - April for $1000/per month in total for $4000. My Forecast should only be Feb-April = $3000. So my total column should only display $3000. Once Feb has passed , the forecast should only be March-April i.e $2000. How to get rolling month sum of forecast once month has passed.
Attached is a sample spreadsheet with different vendors.
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..
I have added a user form to this spreadsheet to make it a little more user friendly to edit/add/delete some information. Now the API used only needs longitude/latitude (lat/lon) to be input by the user. In this case it would be good that for each lat/lon to have a custom name added by the user.
How to link the userform, that's already made, to the information in the spreadsheet?
I was thinking it would be convenient to have the "Site Name" added to a column in the sheet named "Site List" and then have the "Fore Cast Data" pull the names from there.
I am having trouble finding the days on hand for each SKU. I am trying to get a formula to look at how many days will the total SKU Qty cover in the Forecast_qty. I used a vlookup to make sure the sku = sku but i cant get the days on hand number.
I'm trying to create a monthly forecast over 9 months. I know the planned cost for month 1 and month 9 but need to figure out how to spread the remaining amount over months 2-8 using a downward slope.
Here's the problem:I have 9 months to spend $28M. I know in month 1 I plan to spend $6M and month 9 $3M. That leaves $19 for months 2-8. I need to figure out what the burn rate would be in months 2-8 ramping down from $6M to $3M and not exceeding the available amount (19M)
I have project start date in cell C2((MMDDYYYY format).In cell D2 I have put the total days needed to complete the project.In cell E2:E6 I have got the scheduled Holidays.
I need to calculate the project completion date in F2.We work from Monday to Saturday,Sunday being off day.
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)
So I am trying to make a macro that will update monthly forecast data to what the actual production/consumption data was. The production/consumption numbers come in separate workbooks (i.e. "Jan14", "Feb14") and need to be linked to the 'Book' main file. The path needs to the Jan14 needs to come into the 'Book' file, not just the values. I would like to be able to just have a button above each month that will just automatically load that month's data when it becomes available.
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.
I have two columns where TREND does not work well. I want to calculate the TREND value of Column 2 (PERCENTILE RANK) based on a new RAW number. If I TREND between just the two rows, the value is fine (i.e. If I TREND between 105 and 110 RAW, I get the right PERCENTILE RANK if the new X value is 106 (which is 15.3% PERCENTILE RANK). How can I make a formula that will only look at the two rows and TREND just those two rows to give me the right PERCENTILE RANK.
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).
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..
I have a simple x y scatter graph, to which i have a trend line running through it, fine.
what i need to do is fine some values according to that trend line. e.g. my X axis goes up to 60 and my Y axis goes to 10, so if i draw accross at 55 from the trend line, i then go down to a number on my Y axis.
