Forecast Linear Trend Between Min & Max
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
ADVERTISEMENT
Aug 29, 2013
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.
View 2 Replies
View Related
Feb 10, 2008
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.
View 2 Replies
View Related
Jun 16, 2008
I used HTML Maker to show my sheet below. I want to have cells K3:Z3 automatically populated using the same calculations as "Linear Trend" Auto Fill and K4:Z4 using "Growth Trend" Auto Fill without having to do it manually. The data in Row 2 will of course be continually updated with actual scores so the remaining empty month scores would need to be updated again. I am using this to come up with a more accurate prediction method than a simple trend line in Excel graphs.
******** ******************** ************************************************************************>Microsoft Excel - Auto Fill setup for questions.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutL14=ABCDEFGHIJKLMNOPQRSTUVWXYZ1Months00.030.10.250.512345678910111215182124273033362Score10 1010 9887 3Linear Predicted10 1010 9887 4Growth Predicted10 1010 9887 5Auto filled Linear in Red10 1010 98877.066.666.275.885.485.094.74.33.913.513.122.732.331.941.551.156Auto filled Growth in blue10 1010 98877.146.826.526.235.955.695.435.194.964.744.534.334.143.953.783.617Actual scores10 1010 98877554456 Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 26 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
Jul 21, 2006
I was handed the attached file. understand everything except how the values in row 6 were derived No formula present when I received the file, just the numbers. Row 7 is hard entered scores the units achieved
View 8 Replies
View Related
Feb 10, 2007
need an excel simple formula or function that returns values of y for given x in a two-dimensional array and works in the following way.actually a combination of linear interpolation and lookup/linear search. as in following example
x y
1 10
2 15
3 20
if x=1.5 then y=12.5 (answer available thru FORECAST but incomplete)
if x=2 then y=15 (answer available thru LOOKUP but incomplete)
so for whatever the x i need/get a valid y thru a single formula/method !
sorry m not good in math,
View 9 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
Oct 16, 2008
I would like to run a multiple linear regression in vba. I have one dependent and three explanatory variables. I will have to use a macro of some kind, since I need to run too many regressions to do it manually. To simplify things a little bit:
- There will always be exactly three independent variables
- There are no missing values
- The data is allways numerical
I've already got four ranges defined: Yrange, X1range, X2range, X3range. I would like to take these ranges as input parameters for the regression model. The only two parameters I need are Sum Square for Regression (SSR) and the degrees of freedom. I understand that you can use excel's matrix formulas to calculate some of the input parameters, but one doesn't really get around vba. Any (simple) source code allowing me to conduct a regression with three input parameters?
View 7 Replies
View Related
Mar 29, 2013
I have to convert the first to the second program, but i don't know how to do this:
MAD.png
ABStolinear.xlsx: the first sheet is the first program which is correct (i think), the second sheet is a failed attempt to write the second program
View 5 Replies
View Related
Feb 10, 2009
I have 3 equations and have created the relevant chart from them. What I would like to do is put in a value of x & y and get result z. Having done a search through previous threads I found some BiLinear Interpolation code that works using the table in a workbook, but what I'd really like is the Interpolation of the formulae rather than the resulting table and for it to be embedded in VBA.
View 6 Replies
View Related
Oct 4, 2007
In the attached sheet, I have measured data from a device. It shows Input, Output, gain and delta. You can see as input increases, delta comes down. I want to find the output corresponding to delta od -1. In the table, I have enteries for delta of -0.982 then -1.382, not for -1. How to find output corresponding to delta of -1?
View 6 Replies
View Related
Oct 9, 2008
I need a formula to calculate a linear sliding scale from 10%-30% (no higher, no lower). Based on the data in the below chart, if the GROR (let's say this is known and the result resides in cell A1) is less than or equal to 0.0% the payout % is 10.0% and gradually increases as the GROR increases.
The payout % is capped at 30% which is if GROR is 18% or greater. Below are just some examples of how certain thresholds should calculate.
GROR 0.0%5.0% 8.0% 11.0% 14.0% 18.0%+
payout %10.0%10.0%14.6%19.2%23.8%30.0%
View 2 Replies
View Related
Jan 27, 2013
If I have a set of numbers 1,2,3,4,5,6,7,8,9 in cells, how can I get a line of best fit, linear regression, in the cells next to them?
View 1 Replies
View Related
Nov 25, 2006
I have done quite a bit of looking on Google and looked over the posts in this forum, however, I can't find an example Excel worksheet for a linear weighted moving average.
The data set I am applying this to has 180 data points and the linear weights should extend back over the last 30 points.
View 9 Replies
View Related
Mar 8, 2008
in this example below i need to work out what the divsor would be for values inbetween the data?
View 9 Replies
View Related
Mar 20, 2008
I have several hundred points in cartesian x,y,z coordinates. I used an Excel add-in program to calculate the equation of best fit to the points. The equation is:
z=A*x+B*y+C
where (for this case):
A= 2.29091
B= -1.77338
C= -192.494
My objective is to define the several hundred x,y,z points in cartesian coordinates about a new cartesian coordinate system whose origin is the centroid of the data points and whose z plane (ie z=0) is equal to the best fit plane defined above (when referenced from the original coordinate system).
Using Pro/e (a cad program), I was able to create the new coordinate system using the x, y, and z intercepts from the best fit plane, and then was able to redefine the points about the new coordinate system. After the points were redefined in Pro/e, I was able to export the points back to Excel; however, I would prefer to only use Excel / VBA because I prefer the calculations be transparent for optimal accuracy and validation.
View 9 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
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
May 5, 2014
I have set up a linear regression array in Excel and now want to test the significance of my r2 value at a certain level of significance. I've only been able to find tables that give the critical r value, but I want to test it at 99.73% level of confidence and none of them contain that specific value. Is there a way to do this in Excel?
View 3 Replies
View Related
Mar 2, 2009
I have a set of data. I know how to do linear regression over the whole set of data. How do I have another linear regression over the first 5 points in the set of data on the same graph ?? I am using Excel 2007
View 14 Replies
View Related
Nov 18, 2012
Writing a macro to run SOLVER on a linear program. I have twenty tabs, each with a linear program and a solver already set up (constraints, cells to change, and cell to maximize). I would like to run a sub that will solve each tab's LP by running the tab's SOLVER...
If it makes it easier, my cell to maximize is I17, by changing B16:H16, subject to I20:I40 SolverAdd -> SolverOk -> SolverSolve
View 1 Replies
View Related
Apr 18, 2008
I would like to find the blanks within a column. Using the available cell values above and below compute a sort of linear function then, using that linear function fill in the blanks in between. So the values are linearly increasing or decreasing (whatever the case maybe) to the next available nonblank cell value. spreadsheet attached.
View 3 Replies
View Related
May 10, 2008
I would like to find the intersection points of all of the linesshown on the included chart. If that could be output into a table, by the day, that would be ultimately what I am after. http://bladeseri.50megs.com/Intersects.png. Guess 50megs.com doesn't allow this type of reference. Here is Geocities link http://www.geocities.com/bladex2007/Intersects.png
View 5 Replies
View Related
Jan 31, 2014
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.
Rolling sum after month has passed.xlsxâ
View 5 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
Aug 9, 2013
I've attached a file that is the result of a product forecast.
What I'd like to do is minutely adjust the data in B4:K12 so that the totals shown in blue colour on Row 15 and Column O are respected.
Book1.xlsx
View 3 Replies
View Related
Jun 25, 2014
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.
View 13 Replies
View Related
Jul 12, 2012
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.
SKU
Total
DOH
ITEM_CODE
FORECAST_DT
FORECAST_QTY
596459
450
?
596459
7/11/2012
54
[Code] ............
View 1 Replies
View Related
