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".
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 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 RelatedSo 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 RelatedI 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
The trend Equation on a chart is of the form y= a+x.(b+c.x) I want to use that equation in a spreadsheet to calculate value of y for the value of x. I can do it manually by inserting the a+x.(b+c.x) in a cell but would like it to be automatic as the a, b, c in the Equation changes regularly as more data is acquired.
View 2 Replies View RelatedI have a user defined function in one VBA module that I'd like to call from another module, so I would normaly scope it as "Public".
However, as I don't want it to be available to Excel as a worksheet function, I would also normally scope it as "Private"
Is there a way to have a function that is available to other VBA modules in the same project, but not to Excel as a worksheet function?
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.
RAW PERCENTILE RANK
10514
11020.4
11121.8
11729.5
11833
12342.5
12444.5
12954.4...........
I'm trying to figure out how to do seasonal forecasting based on volumes. I tried to use FORECAST but the numbers are not accurate.
I have actual data to work with to come up with my forecasting on a monthly basis. Our yearly calender is from Nov 08 to Oct 09.
Here's a sample of the data....
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.
What I have is a column (column A) that only has two data entries at any one time.
I need column B to fill in the trend of those two data points.
My problem is I don't know how to deal with the cells that say #N/A. Which is every cell other than the two cells with data.
If I replace the #N/As with say 1, then it messes up the trend data points.
It seems like I need to be using the trend command, but any solution would be welcome.
1. I use a number of x,y pairs
2. From x,y pairs I produce "scatter" type chart
3. From the chart I get trend line and its equation e.g. 5-order polinominal
4. I convert equation into excel formula
5. I apply formula onto the same range of x argument and produce graph
6. The graph is very much different from the trend line!!! I would expect exact match as I use equation/formula for the trend line.
I need to analyse trends between a range of cells. The idea is to display an image of a triangle pointing up if the percentage between the current month and the previous is bigger, a triangle pointing down if the percentage has decreased or a rectangle if both months are the same. I've tried to build a userform to ask the user to input the range and then analyze the data...with no luck at all.
View 9 Replies View RelatedI 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 am trying to use the TREND worksheet function on a vba array. I keep getting Runtime error 1004: Unable to get the TREND property of the WorksheetFunction Class. I can bypass the error (like it were a search with no result), but I get no result of any kind from the function. My search of other threads yields nothing concrete, but causes a guess that it may have to be used on a worksheet range not a vba array. But that's not actually stated in any of the threads, and I find no documentation to that effect. Is this the issue or is it something else altogether?
Sub test()
Dim knowny As Variant
Dim knownx As Variant
Dim newx As Integer
Dim myval As Integer
knowny = Array(2, 4, 6, 8, 10)
knownx = Array(1, 2, 3, 4, 5)
newx = 6
'On Error Resume Next
myval = Application.WorksheetFunction.Trend(knowny, knownx, newx)
' 'next error, err as normal.
' On Error GoTo 0
' Err.Clear
Debug.Print myval
End Sub
Ive got an inventory of about 2000+ items as well as items that can be broken down into more specific groups such as colors, designs and sizes. What i want to know is how to get the correlation, thats excel function CORREL(), of each item for the time weve had it. The data I can provide is the monthly quanitity sold of the item.
For example I have item A.
Sold Month
5 1
7 2
4 3
5 4
and so on. Easy enough to do. My only problem is I would have to manually do that 2000+ times for my entire inventory to figure that out. What I wants is a command that can verify my item code, which is simple enough as I can convert the item code to something as simple as a string or leave it as is. Not every item has been in the system an equally long time though. If that were the case i could just copy it with blank cells to fill the space and copy and paste all the way down. So I need to find a way to get an IF statement to correlate the corresponding cells IF the item number matches the target cell. What I have right now is something to this extent.
=IF('2'!$A:$A,A1,CORREL('2'!$B:$B,'2'!$E:$E))
On top of these graphs, i draw a line (insert a line shape) corresponding to a certain gradient in the data presented. I do not want this line to be exact (i.e. not point to point) as it is best to be drawn by eye so that the general 'gist' of the data can be found. What i would like, using any method available, is to be able to find the gradient of this line i have just drawn which obviosuly must be done taking into account the scale of the graph. The scales of the graph will not be consistent across all applications.
View 4 Replies View RelatedI've created a graph from data and need to add an average/net impact/trend line to the graph. From what I've read this needs to be an XY scatter, however, several efforts later and I can't seem to get it to work for me.
0514_Costs_DRAFT_ExcelF.xlsx
i have a stacked bar chart, similar to this: [URL] What i need is a trend line, similar to this: [URL] that shows the average of the bottom bar to date.
View 14 Replies View RelatedOk so heres an example workbook:
Book5.xlsx
As you can see from the workbook, each column's amount of numbers depends on the data pulled from an index/match function.
These numbers will change and I want the TREND function to dynamically change with the data.
I'm having difficulty figuring out how to line graph two separate dates. I also want to show a trend.
I was first thinking I would need two graphs, but is there a way to merge into one?
I am trying to fit a cooling graph with a trendline and I have found the power line to fit the best, as well as corresponding with Newton's law of cooling. However, I am not savvy enough to discuss how similar or dissimilar the two equations are!
the resulting equations from two different curves are as follows:
y=4305 x^-0.87733
y=4025 x^-0.91004
both fits have an R^2 of 0.996.
I am analyzing two scenarios of cooling the same object with three variables, one intended (the extraction of energy using water flow in one case, and no extraction in the other) and the others not under my control: different starting temperatures and different ambient temperatures. I need to understand if these are generally the same curve, or how very different are they? is there any statistical way to represent their relationship?
I want to show a stock chart in Open-High-Low-CLose format, then I hope that a trend line can be displayed at the same chart, what can I do? It seems like that stock chart can't be merged with another chart
View 7 Replies View RelatedIs there a way to use Conditional Formatting in Excel 2007 to do trend analysis.
For example, I'm lookin at P&L statements that show finanicial information for the last 12 months.
I want to highlight cells that have increased (or decreased) more than 3 straight times.
As an example if I have:
Jan - 5000
Feb - 6000
Mar - 5650
Apr - 5700
May - 5900
Jun - 6100
Jul - 5000
Aug - 5200
Sep - 4900
Oct - 5100
Nov - 4200
Dec - 4500
I would want the cells from Apr, May and Jun to be formatted differently (i.e. red) because they consecutively increased for 3 months.
I need to create some trend analysis for Vehicle sales. I have attached a file with sample data.
There are few problems i am facing like description has all the vehicle details which are not in any particular order, even if some how i manage to separate these then too many variables are not letting me come to any conclusion for example make, model, mileage, year of registration color of vehicle, auction sold, sale price, cap (bench mark for pricing).
what i need to produce is which auction sells certain vehicles best or which color of certain model sells best.
[URL]
Is there a way to get an equation for a trend line and have excel automatically export the equation to a cell where it can be used. The only way i currently know how to do this is to manually type the equation.
View 8 Replies View RelatedI am trying to find the exponential equation from a set of data in excel. The problem is that the graph does not follow the scatter plotted points on the graph. How would you remedy this?
View 1 Replies View RelatedI was asked to make a trend graph showing activation's and deactivation's over time. My boss wanted it to be done automatically when you placed values into a simple excel sheet like the attached image (the graph would be below this).
View 14 Replies View RelatedI'm trying to create a formula where I can show the trend of two values from one month to the next that shows wheather the results went up or down in a positive or negetive value. For example:
If I have a transfer % of 18% in October and November MTD is at 17% - the arrow would then reflect downward showing in green, as the lower percentage is better. but I need the arrow to show upward in Red if say the value for November MTD was 19% instead.
I tried conditional formatting and attempted an "If" formula. I must not have all the information to create the formula as it errors out on me. I can create it if only looking to reflect one arrow...
=if(c3<b3,"")
I am having trouble copying a charts trend formula into a cell.
the best I can do is select it as follows:
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
I know you can work it out using linest, but this chart uses a polynomial to the degree of 3 to 5.