I am doing a multiple state based analysis project. I can have thousands of
orders over multiple states (one, none or multiple orders per state are all
valid result possibilities). The result I am working with is an order
completion time (2.44 days, 0.56 days or 30.25 days for example) from a
master list.



I can determine the 90% rank of the entire list as follows:



"=SMALL('OOS Detail'!H:H,ROUNDUP((COUNT('OOS Detail'!H:H)*0.9),0))"



I have three product recaps that I create independent data columns for to
return the rank by product.



The problem I am having is I need to return 90% rank by state and I do not
want to have to create 50 independent data columns foreach state each week I
run the report. I cannot figure out how to get my formula above, which works
when based on a single state criteria to work when the list contains multiple
states.



I think I need to "=IF" by state variable and return the above formula rank
result by state from the original master list of values.

The data file is simple, one column state name (AL,AR,etc) and a
corresponding result value (1.01,2.02,etc). Maybe I am using the wrong
approach so suggestions are appreciated.

Goal: return the x% variable value (90%, 95%, etc) from a list of numeric
values for an entire list of values and for the same list of values based on
product and state variables.

State Result Product
AL 1.06 A
AL 2,o7 B
AR 1.99 A