1
$\begingroup$

I'm looking at a large collection of values. These values follow an exponential distribution. I have their probabilities and a pre-made Excel spreadsheet that will auto calculate expected values from values that I put into the spreadsheet.

Now, my question is related to what seems very much close to the idea of standard deviation, chi squared test, and percent error. I want to create a column in Excel next to each.

| Name    | Expected | Observed | Percent Error |
|---------|----------|----------|---------------|
| Thing1  |          |          |               |
| Thing2  |          |          |               |
| Thing3  |          |          |               |
| Thing4  |          |          |               |
| Thing5  |          |          |               |
| Thing6  |          |          |               |
| Thing7  |          |          |               |
| Thing8  |          |          |               |
| Thing9  |          |          |               |
| Thing10 |          |          |               |
| Thing11 |          |          |               |
| Thing12 |          |          |               |
| Thing13 |          |          |               |
| Thing14 |          |          |               |

I've already created the percent error column which made me realize what the problem is. I have some results in that column that are 100% different from each other because expected is roughly 0.5 and observed is 0. Then I have some results where the expected is 1605 and the observed is 1533. So the percent difference is 5% when there's a difference of 72. So I'm trying to think of something that is more relative to the values.

$\endgroup$
  • $\begingroup$ What exactly is your question? Although you have told us what it is "related to," I cannot perceive a specific question in the text. $\endgroup$ – whuber Feb 20 '15 at 17:06
  • $\begingroup$ Sorry, I thought I had made that clear in the last sentence. In the best way I can think to state it, I'm trying to figure out a method to show how far off the values really are from the distribution. 0.5 and 1 aren't very far off of the distribution and neither are 1600 and 1550 but they yield completely different percent differences. I need something that is relative to the distribution. $\endgroup$ – David Feb 20 '15 at 17:08
  • $\begingroup$ Usually we subtract the two values to do that. What's missing here is a clear statement of what you mean by "relative to the distribution": that phrase by itself is too vague to lead to an objectively answerable question. $\endgroup$ – whuber Feb 20 '15 at 17:11
  • $\begingroup$ I have a column in there for difference too just to show anyone who might not be paying attention what that is along with the percent error. I'll use chi square test to try and explain. If I had a set of results that had known probabilities then I could show how well the results fit the distribution or if something was out of place. I don't want to use chi square to get a result of whether all the data points are a close enough fit to the distribution, but rather whether each observed value is close enough to its expected value to be found in that distribution. $\endgroup$ – David Feb 20 '15 at 17:17
  • $\begingroup$ Its also possible that I'm thinking of this in completely the wrong way. $\endgroup$ – David Feb 20 '15 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.