[Solved] Data Analysis - But which tools?

[Wes-at-UoB]

Hi All,

I have 2 sets of data (sample size: 150 each), one is measured values obtained from experiment, whilst the other is estimated values determined from a prediction model.

If I want to test the accuracy of the prediction model, which analysis tools from 'data analysis' function should I use?

Regards.

[Glenn Kennedy]

Correlate one against the other and look at r2....

[ChemistB]

I would also suggest doing a t-test (which probably means activating the "Analysis ToolPak") Here's a good web site discussing correlation and t-tests

http://www.upa.pdx.edu/IOA/newsom/da...%20t%20phi.pdf

[MrShorty]

Measuring goodness of fit is an important part of statistically analyzing a prediction model. I would venture to say that most of your question is more statistics than Excel.

If you know what quantity you want to use to measure "goodness of fit", then explain to us what that quantity is and we'll help you calculate that quantity in Excel.

If you do not know what quantity you want to use to measure "goodness of fit", then I would suggest that you take that question to someone more qualified in statistics to help you understand how you should measure goodness of fit. Once you understand the statistical side of the question, bring it back to us, and we can help you program that into Excel. I don't know that we have any statisticians here who are good enough to make a definitive suggestion.

As Glenn notes, r2 (and other values related to the Pearson correlation coefficient) are frequently used to measure goodness of fit. In my work, I tend to use a root-mean-square error to measure goodness of fit. I know there are other measures out there as well. There are many ways to measure goodness of fit.

Links:
https://en.wikipedia.org/wiki/Pearso...on_coefficient
https://en.wikipedia.org/wiki/Root-m...uare_deviation
https://en.wikipedia.org/wiki/Goodness_of_fit
http://www.real-statistics.com/correlation/

[Wes-at-UoB]

Thanks for your reply, Glenn.
Indeed, that was my first step - plotting them in a graph and finding the R2.

But I was thinking to have more statistical indicators from Excel.

[Wes-at-UoB]

I would also suggest doing a t-test (which probably means activating the "Analysis ToolPak") Here's a good web site discussing correlation and t-tests

http://www.upa.pdx.edu/IOA/newsom/da...%20t%20phi.pdf

Thanks for your reply. The website is pretty good!

[Wes-at-UoB]

Measuring goodness of fit is an important part of statistically analyzing a prediction model. I would venture to say that most of your question is more statistics than Excel.

If you know what quantity you want to use to measure "goodness of fit", then explain to us what that quantity is and we'll help you calculate that quantity in Excel.

If you do not know what quantity you want to use to measure "goodness of fit", then I would suggest that you take that question to someone more qualified in statistics to help you understand how you should measure goodness of fit. Once you understand the statistical side of the question, bring it back to us, and we can help you program that into Excel. I don't know that we have any statisticians here who are good enough to make a definitive suggestion.

As Glenn notes, r2 (and other values related to the Pearson correlation coefficient) are frequently used to measure goodness of fit. In my work, I tend to use a root-mean-square error to measure goodness of fit. I know there are other measures out there as well. There are many ways to measure goodness of fit.

Links:
https://en.wikipedia.org/wiki/Pearso...on_coefficient
https://en.wikipedia.org/wiki/Root-m...uare_deviation
https://en.wikipedia.org/wiki/Goodness_of_fit
http://www.real-statistics.com/correlation/

Thanks for your reply MrShorty.
I thought the Analysis ToolPak might have the exact ready-made function that I am looking for.
I happened to discuss my problems with a chap who is good in stats. I was told to find the Bias (estimated value - observed value) and Root mean squared error (which is the one that you mentioned) and express them both in their unit and percentage.
I was wondering if these can be found in the Analysis ToolPak?

Regards.

[MrShorty]

List of available tools in the analysis toolpak: https://support.office.com/en-us/art...b-898f4c90c007 I do not see bias or RMS error listed. Though, to be fair, the formulas for these are pretty simple.

If I understand what you friend has in mind, these are possible formulas in two additional columns:
bias (unit basis): =b2-a2 (or whatever the Excel references are). using relative references, this can easily be copied down to cover the entire data set.
bias (per cent basis) =cell reference to bias (unit basis)/a2. copied down
rmsd (unit basis): =sumsq(column with bias unit basis) or =sumxmy2(estimateds, measureds)
rmsd (per cent basis): =sumsq(column with bias per cent basis)

list of excel functions if you need help entering the sumsq() function or sumxmy2() function: https://support.office.com/en-us/art...rs=en-US&ad=US

Your colleague sounds like a better statistician than me. If he has a different idea of what these quantities should be, then I would defer to his judgement.

ONedit: RMSD should include counting the number of observations, and, in my sloppiness, neglected to include that. Be sure to review the formula for RMSD in Wikipedia (see link above) to make sure you get the formula correct.

[Wes-at-UoB]

List of available tools in the analysis toolpak: https://support.office.com/en-us/art...b-898f4c90c007 I do not see bias or RMS error listed. Though, to be fair, the formulas for these are pretty simple.

If I understand what you friend has in mind, these are possible formulas in two additional columns:
bias (unit basis): =b2-a2 (or whatever the Excel references are). using relative references, this can easily be copied down to cover the entire data set.
bias (per cent basis) =cell reference to bias (unit basis)/a2. copied down
rmsd (unit basis): =sumsq(column with bias unit basis) or =sumxmy2(estimateds, measureds)
rmsd (per cent basis): =sumsq(column with bias per cent basis)

list of excel functions if you need help entering the sumsq() function or sumxmy2() function: https://support.office.com/en-us/art...rs=en-US&ad=US

Your colleague sounds like a better statistician than me. If he has a different idea of what these quantities should be, then I would defer to his judgement.

ONedit: RMSD should include counting the number of observations, and, in my sloppiness, neglected to include that. Be sure to review the formula for RMSD in Wikipedia (see link above) to make sure you get the formula correct.

Thanks for your reply.

I have found the bias and RMSE, though I must confess it was a much looooooooooooooooooooooonger way than yours (sumxmy2() does the trick there!)

I am not sure if you are aware of this:
as the denominator is the observed value in % calculation, the output becomes infinity when it is zero.

At the moment, I disregard those zero values in bias and rmse calculation (unit and %).

[MrShorty]

Zero (0) often creates difficulties in mathematics. Chuck Norris might be able to divide by 0, but the rest of us poor saps cannot.

Whether or not to ignore 0 results in your calculations will probably depend on exactly what 0 means. In my work, 0 rarely has real meaning. It usually means "below threshold" or "too small to measure" or something like that -- it does not mean "a value of exactly 0". Or it will mean something trivially true, like in a blank run (I put 0 into the experiment and get 0 out of the experiment). You have to work from you knowledge of the experiment to know if 0 means "this result has a value of 0" or if 0 means something else.

If you believe that the 0 result has real meaning, then it may not be wise to simply ignore it. You may need to consider some other measure of "goodness of fit" that better handles 0 values.

[Wes-at-UoB]

Thanks for your reply again.

It certainly helps me lots!