I want to create a graph in excel and get my spread sheet to analyse the graph and give me the answer?

Question

Hey,

I'm setting up an excel programme that will calculate the effort allocation in a search and rescue situation at sea. This includes a percentage known as the probability of detection (POD) which has to be read off a graph.

I want to create the same graph in excel and get my spread sheet to analyse the graph and give me the answer without looking at the hard copy of the graph.
It's hard to explain but I do not want to create a graph using the data in the spread sheet. I want the data the query the answer from the graph that I will create seperately using other data and I want the answer to appear in the spread sheet.

Is it possible?? If so....how????

Answer 1

If your graph is linear, then it would be very easy.You would put the coordinates for the XY data point for the start of the graph in two cells. In the two cells below those, put the XY coordinates for the end point on your graph. Then use the function FORECAST to determine any data point from a linear line created by the two graph points you entered.



If your graph is not linear, you would first have to derive a formula for plotting your graph and then use that formula to calculate for a specific data point. You can use Excel to derive the graph formula.



To derive a formula you have to recreate your graph in Excel. Note: once you've derived the formula, you can get rid of the recreated graph. It is only needed to get the formula.



To make the graph, put in a column as many X data points from your graph as you can, and in the adjacent column put the corresponding Y coordinates. Select all the data and make an XY Scatter plot sub-type data with connected lines.



Right-click on the line on the graph and select Add Trend-line. Select the type of trend-line that best fits your data. On the same dialog under the Options tab you can check the check-box to "Display Equation on Chart". With this equation, you can calculate any Y value from a given X value. The accuracy is dependent on how well the trend-line fits your data.

Answer 2

The data to be analyzed has to come from somewhere, but it doesn't necessarily have to come from spreadsheet cells you populate manually. It is conceivable that you could have the raw data in an external file (perhaps in comma-separated-variable format, or .CSV), and you could use a macro to read it into a range of cells. From that point, you could have separate macros for developing a chart view of the data and for deriving your "answer" through programmatic analysis. The only thing you need to be certain of is that the "answer" can be arrived at algorithmically so you can write a macro to do it.

Answer 3

Excel works the other way round. Graphs are generated from data.

Answer 4

Microsoft Spreadsheet.