Yea, I know there are a lot of Excel formula requests, but I'm stumped on this one - but them I'm fairly new to Excel formulas. I've googled a fair bit, but not found anyone who is trying to do something like this. Please note this is for Office XP.
I need a formula which will calculate the Standard Deviation of some results as compared to a fixed value. To amplify: I'm gathering data where the nominal "correct" value is 100. Measured values will range up to about 10 either side of that, so from about 90-110. In the very long term I would expect the average to be about 100, so a standard =STDEVP etc will work just fine. The problem is, it's not the very long term yet. Currently almost all of my values are one side of the magical 100 value (I have four of these to sort out). Thus one set of data might read:
102
102
104
103
101
100
105
106
102
etc. Now if I use the formula above it will produce a Standard Deviation based around a mean which it calculates. That mean will be about 103, and I'd have an SD of about 1.5 - which is what is happening. But I don't want to know how much the figures vary from their internal mean, I want to know how much they vary from that nominal value of 100. In this case the SD would be bigger, at about (guess) 3.5. If you want an analogy, imagine you want to know the SD of the heights of a group of basket-ball players, compared to the national average.
I've tried "mirroring" the data: creating a set of data points which are exactly as far one side of 100 as these are the other and adding them in, but that produces an SD which seems too high: the 2SD is bigger than all the values present (but not by much).
I'm happy to do this by more than one formula, but I'd like to be able to have a formula(s) which updated with each added bit of data. If it has to be done now and again instead, so be it.
Cheers
M
I need a formula which will calculate the Standard Deviation of some results as compared to a fixed value. To amplify: I'm gathering data where the nominal "correct" value is 100. Measured values will range up to about 10 either side of that, so from about 90-110. In the very long term I would expect the average to be about 100, so a standard =STDEVP etc will work just fine. The problem is, it's not the very long term yet. Currently almost all of my values are one side of the magical 100 value (I have four of these to sort out). Thus one set of data might read:
102
102
104
103
101
100
105
106
102
etc. Now if I use the formula above it will produce a Standard Deviation based around a mean which it calculates. That mean will be about 103, and I'd have an SD of about 1.5 - which is what is happening. But I don't want to know how much the figures vary from their internal mean, I want to know how much they vary from that nominal value of 100. In this case the SD would be bigger, at about (guess) 3.5. If you want an analogy, imagine you want to know the SD of the heights of a group of basket-ball players, compared to the national average.
I've tried "mirroring" the data: creating a set of data points which are exactly as far one side of 100 as these are the other and adding them in, but that produces an SD which seems too high: the 2SD is bigger than all the values present (but not by much).
I'm happy to do this by more than one formula, but I'd like to be able to have a formula(s) which updated with each added bit of data. If it has to be done now and again instead, so be it.
Cheers
M
