percentile from mean and standard deviation excel

PERCENTILE FROM MEAN AND STANDARD DEVIATION EXCEL CODE

#This function measures the difference between our STD and MEAN and the expected values The values we want to learn are the ones of our dataset - we exclude the quartiles, of course, because we already have a contraint for what those values must be. We start the same way as in my previous answer, satisfying all requirements except from MEAN and STD: from math import floorīut then, we treat this as a (constrained) gradient descent problem: we want to minimize the difference between our MEAN and STD and the expected MEAN and STD, while keeping the quartiles values.

PERCENTILE FROM MEAN AND STANDARD DEVIATION EXCEL CODE

Also, this code can be adapted for other value requirements - just change the header. Here's an example with a dataset of size 40, but you can change the value of the COUNT variable if you want to produce a larger dataset. It's significantly slower, so only use it if you don't care about the dataset being small. I just thought of another approach that makes numbers not look so artificial. #We tweak some further decimal points now #Between the 25th and 50th percentile, we can move the values between 32 and 49Ĭurrent_std = math.sqrt(sum()/(COUNT - 1)) #We can move the values between the 0th and 25th percentile between 0 and 16 #The new value for v should be between P75 and MAX so we don't invalidate the percentiles. We simply clear the variable v from that formula. #This equation comes from the formula for the average: AVG = SUM/COUNT. #We solve an equation to get the necessary value for v for the mean to be what we want to be. #This increases the average a bit, but not enough! #This would be much harder to do automatically instead of with a hardcoded example. #That is, numbers between pos 197 and 261. So we manually teak the numbers between the 75th and 100th percentile. #We are getting a mean of 24.84, and we need to increase it a little bit to get 35.790875. #This will give us correct 25%, 50%, 75%, min, max, and count values. #We do something extremely similar with the other percentiles #when calculating percentiles but we can simulate that by letting the next value be also P25 #Actually pandas does some linear interpolation () #We could also interpolate the value from P25 to P50, even adding a bit of randomness. #This will satisfy the 25th percentile requirement #Good, we already satisfied the easiest 3 requirements. The basic idea is satisfy each requirement from easiest to hardest, taking care of not invalidating the previous ones as you advance. It can be adapted for the general case, but expect many 'border cases'. Here is a way of getting a dataset that fullfill's your example requirements. You could create a dataset that has the correct count, mean, min, and percentiles, but the standard deviation is quite a bit tricky. I think it's nontrivial in the general case. Hi and welcome to the forum! This is an excellent question, I loved it.

Percentile from mean and standard deviation excel