Tech Support Guy banner
Status
Not open for further replies.

Using the trend line formula from a chart in Excel

2K views 11 replies 3 participants last post by  tetd 
#1 ·
Hi

I have several chart that represent sensitivity data, and I was hoping I could make an index to clearly show how sensitive the data is.

So is there anyway (maybe using a function/formula or macro in MS EXCEL) to read the formula from a chart, and take (copy) the gradient part of it and use it on its own?

Not sure if I have explained this very well, but if anyone has any clue I would appreciate the help.

Thanks

EDIT: tried recording the action i want to do (this is what I get)

Code:
ActiveSheet.ChartObjects("Sheet1").Activate
    ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
    Range("A1").Select
    ActiveSheet.Paste
but this will only paste what is currently copied, i.e. If I just copied the word "sensitivity", then ran the macro, "sensitivity would be pasted in cell A1, and not the trend line formula.
 
See less See more
#2 ·
I can't speak for others but I honestly can't picture what you want to accomplish.
Could you maybe post a sample graph and and a sample of the result you wish to accomplish?
BTW, don't forget to mention the Excel version, could change things a bit too.
 
#3 ·
Okay before I go about trying to post a sample graph etc.., I'll try explaining myself once more.

Firstly, I am using Excel 2010.

Using 'data validation' I have created a list, using ~15 variables from a tables in another tab, each variable has a cost/value for a percentage change -10%,-8%,-6%,-4%,-25,0,2%,4%,6%,8,10%. I guess this is not too important, but basically this data gives linear graphs for each different variable. And because I only want to show one variable on the chart at a time the chart looks up the cell reference when the 'List' has been set up, so when you select the variable you want it appear on the chart.

Now, I want to be able to assign a number to the gradient of the graph to use as a sort of index. So I was hoping there was a away I could take the gradient out of the trend line formula (automatically) and have it shown n another cell somewhere on a page?

I am starting to think if this can be done, visual basic would need to be involved...

Is this any clearer?

Thanks
 
#4 ·
When you say the "gradient of the graph", are you referring to the slope? The first derivative? If I read what you want to do correctly, you have to do a numerical differentiation. Not sure if Excel will do that as a formula or not, though it seems like it ought to be able to.
 
#5 ·
Yes, it is the slope and yes that would be the first derivative. So when you have y = x +c, I would want the number in front of the 'x'.

I am aware that this formula can be found by adding a trend line and then there is an option to display the equation on the chart.

But I only want the value in front of the x, and I don't want it on the chart, but in some other cell.
 
#7 ·
Ah, I think I may have over complicated the problem, so if this was a table for one of the variables, how would the formula go? (just assume that's A1:K2, I have transposed it as it did nt line up otherwise)

-10%
-8%
-6%
-4%
-2%
0%
2%
4%
6%
8%
10%

and

102.97
103.21
103.46
103.71
103.96
104.2
104.45
104.7
104.95
105.19
105.44

Thanks
 
#10 ·
How would I go about doing that? Also if they were nt scaled would nt the value I get be a factor of ten from 0.2474, not ~0.808?

Or seeing as the X axis is a percentage of change from an original input would i need to find the original input andthen the -10% to 10% value of it?

Thanks
 
#12 ·
You have to scale your x values appropriately.
Would you know why the one calculations always 50 times bigger than the other.

I guess this would mean the scale is by /50, but why would this be? It has no bearing to anything in the tables.

EDIT: ....and what confuses me further, is that I can change the x values to anything i wish, whether it '0' for each of them or 2,4,8,16,32..., the graph/tendline/formula is still the same....
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top