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)
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.
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.
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...
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.
m = dy/dx. What's the problem? You want the instantaneous slope (the slope between two cells in your spreadsheet), so if your cell count is x (perhaps multiplied by a scale variable) and the value in the cell is y then m=(y2-y1)/(x2-x1).
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)
You guys lost me in the middle, I'm just a little good with vba, the mathematics is a different issue.
I'll keep looking and If some vba help is neede I'll jump in again.
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.
Related Threads
?
?
?
?
?
Tech Support Guy
9.9M posts
859.7K members
Since 1998
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!