Can Excel vary a value for me automatically and produce a chart?

Hi guys, hoping you'll able to help me out on this one.

I am currently doing a medical school project on a new type of test which has been introduced into our hospital. The test (called NTproBNP) is a screening blood test for heart failure. Patients have this blood test performed, and then have an echocardiogram (ultrasound of the heart) to see how the results compare - echocardiograms are the gold standard test for detecting heart failure. My project involves assessing how accurate the blood test is in identifying patients with heart failure, and calculating parameters relating to the test (sensitivity, specificity, positive and negative predictive values).

My spreadsheet has a cell containing a value, which is the value of the NTproBNP test cut-off - ie, if your test result is above that value, the test is positive, and below, negative. The manufacturers of the test kit suggest that the cut-off should be 14.75. However, if you vary the cut-off, you can improve (or worsen!) how accurate the test is.

At the minute, the spreadsheet uses the 14.75 value, via a few formulae over many cells, to calculate the test parameters. If you change the 14.75 to some other value, certain patients' test results change, which in turn changes the test parameters - the parameters are the results I'm interested in measuring.

What I would like, if possible, is for Excel to produce a graph, say for cut-off values from 1-100, of cut-off value vs sensitivity. Is it possible to get Excel to vary the value for me between 1-100 (instead of making several copies of the sheet to repeat all the calculations at different values of cut-off), and map that against the calculated sensitivity?

Hope this makes sense, I'll be glad to field any questions.

Hi

If I understood your problem correctly there's no need to make copies of the worksheet.

You have in your worksheet a model that allows you to get a value for the sensitivity given the cut-off value.

Excel has a tool to test the influence of the variation of the parameters in the final result of a model. It's the Data>Table.

Using the Data>Table, you can easily build an input table for the chart.

In this case it will be a DataTable with just 1 input parameter (max of 2 are allowed). You list the values for the cut-off, 1 to 100, and the Data>Table feature will calculate the corresponding sensitivity values using your model.

Check the help for Data>Table. If you have doubts about the implementation post back.

HTH
lecxe

Thanks for your help, but I can't seem to get it to work. The problem is that working out the sensitivity (and other parameters) does not just use a single formula referring back to the cell with 14.75 in it, so I can't set up a data table

The 14.75 is used by one column to work out whether the NTproBNP test result is positive or negative, then another cell compares that result to the echocardiography result to see if both tests had the same conclusion, then another categorises it as a false/true positive/negative etc etc through 5 or so formulas.

I will just input the data manually and chart it, as values from 1-100 in increments of 5 or 10 will not take too long.

Thanks again

... then another categorises it as a false/true positive/negative etc etc through 5 or so formulas.

I'd just like to point out that that's not a problem to the Data>Table. That's usual in models. The formula used in the data table is the one from the result of the model, doesn't even have to refer directly to the cut-off value.

Since you are going to build the chart input table manually you won't need this now. However, if you think you'll have to do this again in the future maybe it's a good idea to explore an automatic solution. If you want to attach a working example maybe we'll find an easy way to do it.

Good luck
lecxe