Excel Formula Wanted
Posted by Curt Deitz on January 24, 2002 7:55 PM
I want to take a group of numbers (rather large + 2000) and determine what percentage of them fall between 20 and 80 percent of a certain number. This is to meet an EPA regulation that says the majority of an analyzer readings have to fall within 20 - 80 percent of the span of an instrument.
Posted by Dan on January 25, 2002 6:20 AM
I might try the following approach.
Let's say your data is in A1:A2000 and the certain number you are comparing it to is in cell D1. In cell B1 use the formula:
=IF(AND(A1/$D$1,=0.8,A1/$D$1>=0.2),1,0)
That will put a 1 in the cell if the number in A1 is within the 80/20 criteria, or a 0 if not. Copy this formula down column B.
Then to find the total percentage of numbers that fall in the 80/20 you would put this formula in a cell and format as a percentage:
=SUM(B:B)/COUNT(A:A)
HTH
Posted by George J on January 25, 2002 6:20 AM
I have assumed that your data is in Column A, B1 is 20%, C1 is 80% and C2 has the figure you want. This makes it easy to alter any data if required. After you have entered this formula in a cell eg C5, hold CTRL, SHIFT and press ENTER on the no. pad. Any problems, just say.
=COUNT(IF(A1:A10000>(C2*B1),IF(A1:A10000<(C2*C1),A1:A10000)))
George
