I apologize, the dataset has many columns but the process I require needs
only two at a time. I need to return the result by state and then as a
seperate operation the result by product. The process should be the same
regardless of the criteria. Any operation will have one set of flag data
(state or product) and one shared set of results data.

Thank you

"Lars-Åke Aspelin" wrote:

On Sun, 7 Sep 2008 08:03:01 -0700, The Hit Man <The Hit
wrote:

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


If your simple data file only have two columns, one for state name and
the other for the result value, where do the products come from?

Lars-Åke