What is this formula called? (I want to do a thing in excel with numbers)

[Tollens]

I want to do a thing in excel with numbers, but do not know what the formula is called.

Suppose I have a sample of random numbers from 1 to 100. I want to capture a certain percentage of the numbers, lets say 85%. How I want to do that is to take a minimum and maximum indicator that moves towards each other leaving the same amount of numbers on the outside.

For example:

Sample: 2,2,3,5,6 .........89,90,91,100
Min: 2
Max: 100

Min moves up a number to 3 and leave two numbers out of range. Now, Max has to move down to 90 in order to leave 2 numbers out of range. This continues until 85% of the numbers are within range. Then I will know the min and max for 85% of the numbers so it is balanced.

I hope that makes sense. Thanks!

 

[Dr.Peterson]

I want to do a thing in excel with numbers, but do not know what the formula is called.

Suppose I have a sample of random numbers from 1 to 100. I want to capture a certain percentage of the numbers, lets say 85%. How I want to do that is to take a minimum and maximum indicator that moves towards each other leaving the same amount of numbers on the outside.

For example:

Sample: 2,2,3,5,6 .........89,90,91,100
Min: 2
Max: 100

Min moves up a number to 3 and leave two numbers out of range. Now, Max has to move down to 90 in order to leave 2 numbers out of range. This continues until 85% of the numbers are within range. Then I will know the min and max for 85% of the numbers so it is balanced.

I hope that makes sense. Thanks!

It sounds like you want to omit the highest and lowest 7.5% of the numbers. So if there are N numbers in all, you'll be sorting them, then taking 0.075N as the first index, and 0.925N as the last index, with some rounding needed. How to round may be the tricky part.

It may not work out exactly as you describe; for example, to take an extreme case, if you had 100 numbers, of which the lowest 50 are all equal to 2, you couldn't omit all the 2's and still have a balanced set of 85!

Things like this are not always formulas, and they do not always have names! But one relevant term is "percentile".

 

[Tollens]

It sounds like you want to omit the highest and lowest 7.5% of the numbers. So if there are N numbers in all, you'll be sorting them, then taking 0.075N as the first index, and 0.925N as the last index, with some rounding needed. How to round may be the tricky part.

It may not work out exactly as you describe; for example, to take an extreme case, if you had 100 numbers, of which the lowest 50 are all equal to 2, you couldn't omit all the 2's and still have a balanced set of 85!

Things like this are not always formulas, and they do not always have names! But one relevant term is "percentile".

Thank you. That gives me something to work with.

 

[BigBeachBanana]

L

I want to do a thing in excel with numbers, but do not know what the formula is called.

Suppose I have a sample of random numbers from 1 to 100. I want to capture a certain percentage of the numbers, lets say 85%. How I want to do that is to take a minimum and maximum indicator that moves towards each other leaving the same amount of numbers on the outside.

For example:

Sample: 2,2,3,5,6 .........89,90,91,100
Min: 2
Max: 100

Min moves up a number to 3 and leave two numbers out of range. Now, Max has to move down to 90 in order to leave 2 numbers out of range. This continues until 85% of the numbers are within range. Then I will know the min and max for 85% of the numbers so it is balanced.

I hope that makes sense. Thanks!

Looks like you want to capture 85% around the median.

1. Sort the data in ascending order.
2. Find the median value. If the size of the data set is even, take the average of the two middle values.
3. Calculate the number of data points you need to include in the 85% interval for a sample size of 100. This is given by 0.85*100 = 85
4. Take half of this number (rounded down to the closest integer if necessary) to determine the number of data points to include on either side of the median. This is given by floor(85/2) = 42
5. Select 42 data points on either side of the median.