| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple State and product based analysis project
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple State and product based analysis project
|
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple State and product based analysis project
Assuming that your result data are all greater than 0. If G is the column where you have the "flag data" and H is the column with your result data (order completion time), and C1 is the value of the flag data for which you would like the 90 percentil, you may try the following formula in cell D1 =LARGE((B:B)*(A:A=C1),ROUNDUP((COUNTIF(A:A,C1)*(1-0.9)),0)) Note that this is an array formula that has to be entered with CTRL+SHIFT+ENTER rather than just ENTER. If you want you can put the 0.9 in a cell that you refer to instead, but in your original formula it was a constant so I just kept it that way. Hope this helps / Lars-ke On Sun, 7 Sep 2008 08:33:00 -0700, The Hit Man wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple State and product based analysis project
The results were not satisfactory but maybe I am doing something wrong. Order State Order TT Group State 90% TT AL 1.04 AL =LARGE((B:B)*(A:A=G2),ROUNDUP((COUNTIF(A:A,G2)*(1-0.9)),0)) AL 4.07 AR =LARGE((B:B)*(A:A=G3),ROUNDUP((COUNTIF(A:A,G3)*(1-0.9)),0)) AZ 0.11 AZ =LARGE((B:B)*(A:A=G4),ROUNDUP((COUNTIF(A:A,G4)*(1-0.9)),0)) AZ 0.11 CA =LARGE((B:B)*(A:A=G5),ROUNDUP((COUNTIF(A:A,G5)*(1-0.9)),0)) AZ 0.18 CO =LARGE((B:B)*(A:A=G6),ROUNDUP((COUNTIF(A:A,G6)*(1-0.9)),0)) CA 0.11 CT =LARGE((B:B)*(A:A=G7),ROUNDUP((COUNTIF(A:A,G7)*(1-0.9)),0)) CA 0.11 DC =LARGE((B:B)*(A:A=G8),ROUNDUP((COUNTIF(A:A,G8)*(1-0.9)),0)) CA 0.11 DE =LARGE((B:B)*(A:A=G9),ROUNDUP((COUNTIF(A:A,G9)*(1-0.9)),0)) CA 0.11 FL =LARGE((B:B)*(A:A=G10),ROUNDUP((COUNTIF(A:A,G10)*( 1-0.9)),0)) CA 0.11 GA =LARGE((B:B)*(A:A=G11),ROUNDUP((COUNTIF(A:A,G11)*( 1-0.9)),0)) CA 0.19 IA =LARGE((B:B)*(A:A=G12),ROUNDUP((COUNTIF(A:A,G12)*( 1-0.9)),0)) CA 0.2 ID =LARGE((B:B)*(A:A=G13),ROUNDUP((COUNTIF(A:A,G13)*( 1-0.9)),0)) CA 0.38 IL =LARGE((B:B)*(A:A=G14),ROUNDUP((COUNTIF(A:A,G14)*( 1-0.9)),0)) CO 0.11 IN =LARGE((B:B)*(A:A=G15),ROUNDUP((COUNTIF(A:A,G15)*( 1-0.9)),0)) CO 0.84 KS =LARGE((B:B)*(A:A=G16),ROUNDUP((COUNTIF(A:A,G16)*( 1-0.9)),0)) DE 0.11 KY =LARGE((B:B)*(A:A=G17),ROUNDUP((COUNTIF(A:A,G17)*( 1-0.9)),0)) DE 0.31 LA =LARGE((B:B)*(A:A=G18),ROUNDUP((COUNTIF(A:A,G18)*( 1-0.9)),0)) FL 0.11 MA =LARGE((B:B)*(A:A=G19),ROUNDUP((COUNTIF(A:A,G19)*( 1-0.9)),0)) GA 0.3 MD =LARGE((B:B)*(A:A=G20),ROUNDUP((COUNTIF(A:A,G20)*( 1-0.9)),0)) IL 0.11 ME =LARGE((B:B)*(A:A=G21),ROUNDUP((COUNTIF(A:A,G21)*( 1-0.9)),0)) IL 0.11 MI =LARGE((B:B)*(A:A=G22),ROUNDUP((COUNTIF(A:A,G22)*( 1-0.9)),0)) IL 0.11 MN =LARGE((B:B)*(A:A=G23),ROUNDUP((COUNTIF(A:A,G23)*( 1-0.9)),0)) IL 0.19 MO =LARGE((B:B)*(A:A=G24),ROUNDUP((COUNTIF(A:A,G24)*( 1-0.9)),0)) IL 0.19 MS =LARGE((B:B)*(A:A=G25),ROUNDUP((COUNTIF(A:A,G25)*( 1-0.9)),0)) IL 0.19 MT =LARGE((B:B)*(A:A=G26),ROUNDUP((COUNTIF(A:A,G26)*( 1-0.9)),0)) IL 0.3 NC =LARGE((B:B)*(A:A=G27),ROUNDUP((COUNTIF(A:A,G27)*( 1-0.9)),0)) IL 0.3 ND =LARGE((B:B)*(A:A=G28),ROUNDUP((COUNTIF(A:A,G28)*( 1-0.9)),0)) IL 0.3 NE =LARGE((B:B)*(A:A=G29),ROUNDUP((COUNTIF(A:A,G29)*( 1-0.9)),0)) IL 0.95 NH =LARGE((B:B)*(A:A=G30),ROUNDUP((COUNTIF(A:A,G30)*( 1-0.9)),0)) IL 0.95 NJ =LARGE((B:B)*(A:A=G31),ROUNDUP((COUNTIF(A:A,G31)*( 1-0.9)),0)) IL 0.95 NM =LARGE((B:B)*(A:A=G32),ROUNDUP((COUNTIF(A:A,G32)*( 1-0.9)),0)) IL 1.06 NV =LARGE((B:B)*(A:A=G33),ROUNDUP((COUNTIF(A:A,G33)*( 1-0.9)),0)) IL 1.06 NY =LARGE((B:B)*(A:A=G34),ROUNDUP((COUNTIF(A:A,G34)*( 1-0.9)),0)) IL 1.06 OH =LARGE((B:B)*(A:A=G35),ROUNDUP((COUNTIF(A:A,G35)*( 1-0.9)),0)) IL 1.12 OK =LARGE((B:B)*(A:A=G36),ROUNDUP((COUNTIF(A:A,G36)*( 1-0.9)),0)) IL 1.57 OR =LARGE((B:B)*(A:A=G37),ROUNDUP((COUNTIF(A:A,G37)*( 1-0.9)),0)) IL 2.15 PA =LARGE((B:B)*(A:A=G38),ROUNDUP((COUNTIF(A:A,G38)*( 1-0.9)),0)) IN 0.25 RI =LARGE((B:B)*(A:A=G39),ROUNDUP((COUNTIF(A:A,G39)*( 1-0.9)),0)) IN 2.06 SC =LARGE((B:B)*(A:A=G40),ROUNDUP((COUNTIF(A:A,G40)*( 1-0.9)),0)) IN 3.06 SD =LARGE((B:B)*(A:A=G41),ROUNDUP((COUNTIF(A:A,G41)*( 1-0.9)),0)) MA 1.13 TN =LARGE((B:B)*(A:A=G42),ROUNDUP((COUNTIF(A:A,G42)*( 1-0.9)),0)) MD 0.22 UT =LARGE((B:B)*(A:A=G43),ROUNDUP((COUNTIF(A:A,G43)*( 1-0.9)),0)) MD 0.23 VA =LARGE((B:B)*(A:A=G44),ROUNDUP((COUNTIF(A:A,G44)*( 1-0.9)),0)) ME 1.17 VT =LARGE((B:B)*(A:A=G45),ROUNDUP((COUNTIF(A:A,G45)*( 1-0.9)),0)) ME 3.28 WA =LARGE((B:B)*(A:A=G46),ROUNDUP((COUNTIF(A:A,G46)*( 1-0.9)),0)) MI 0.11 WI =LARGE((B:B)*(A:A=G47),ROUNDUP((COUNTIF(A:A,G47)*( 1-0.9)),0)) MI 0.86 WV =LARGE((B:B)*(A:A=G48),ROUNDUP((COUNTIF(A:A,G48)*( 1-0.9)),0)) MI 1.27 WY =LARGE((B:B)*(A:A=G49),ROUNDUP((COUNTIF(A:A,G49)*( 1-0.9)),0)) Assuming that your result data are all greater than 0. (True) If G is the column where you have the "flag data" and H is the column with your result data (order completion time), (My sample Column A = individual order state result and Column B = individual order completion time) and C1 is the value of the flag data for which you would like the 90 percentile, (Column G G2 = AL, G3 = AR, etc) you may try the following formula in cell D1 I modified the formula to read as below in red as I did not clearly understand your reference values: "=LARGE((B:B)*(A:A=G2),ROUNDUP((COUNTIF(A:A,G2 )*(1-0.9)),0))" Compared to your original below: =LARGE((B:B)*(A:A=C1),ROUNDUP((COUNTIF(A:A,C1)* (1-0.9)),0)) Note that this is an array formula that has to be entered with CTRL+SHIFT+ENTER rather than just ENTER. If you want you can put the 0.9 in a cell that you refer to instead, but in your original formula it was a constant so I just kept it that way. "Lars-Åke Aspelin" wrote: Assuming that your result data are all greater than 0. If G is the column where you have the "flag data" and H is the column with your result data (order completion time), and C1 is the value of the flag data for which you would like the 90 percentil, you may try the following formula in cell D1 =LARGE((B:B)*(A:A=C1),ROUNDUP((COUNTIF(A:A,C1)*(1-0.9)),0)) Note that this is an array formula that has to be entered with CTRL+SHIFT+ENTER rather than just ENTER. If you want you can put the 0.9 in a cell that you refer to instead, but in your original formula it was a constant so I just kept it that way. Hope this helps / Lars-Åke On Sun, 7 Sep 2008 08:33:00 -0700, The Hit Man wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple State and product based analysis project
If you use the entire columns in the formula, like B:B, you can not
have any (non numerical) headers on row 1. I suggest that you change B:B to B2:B49 and A:A to A2:A49 in two places. Like this: =LARGE((B2:B49)*(A2:A49=G2),ROUNDUP((COUNTIF(A2:A4 9,G2)*(1-0.9)),0)) Adjust the 49 to fit your data size. Don't forget to enter the formula as an array formula. I got reasonable output using your sample date, e.g. IL got 1.67 All other states have less than 10 samples so you just get to top score if you use 90%. By changing 0.9 to 0.5 I got the result 2.06 (the median) for IN as expected. Lars-ke On Sun, 7 Sep 2008 19:55:02 -0700, The Hit Man wrote: The results were not satisfactory but maybe I am doing something wrong. Order State Order TT Group State 90% TT AL 1.04 AL =LARGE((B:B)*(A:A=G2),ROUNDUP((COUNTIF(A:A,G2)*(1-0.9)),0)) AL 4.07 AR =LARGE((B:B)*(A:A=G3),ROUNDUP((COUNTIF(A:A,G3)*(1-0.9)),0)) AZ 0.11 AZ =LARGE((B:B)*(A:A=G4),ROUNDUP((COUNTIF(A:A,G4)*(1-0.9)),0)) AZ 0.11 CA =LARGE((B:B)*(A:A=G5),ROUNDUP((COUNTIF(A:A,G5)*(1-0.9)),0)) AZ 0.18 CO =LARGE((B:B)*(A:A=G6),ROUNDUP((COUNTIF(A:A,G6)*(1-0.9)),0)) CA 0.11 CT =LARGE((B:B)*(A:A=G7),ROUNDUP((COUNTIF(A:A,G7)*(1-0.9)),0)) CA 0.11 DC =LARGE((B:B)*(A:A=G8),ROUNDUP((COUNTIF(A:A,G8)*(1-0.9)),0)) CA 0.11 DE =LARGE((B:B)*(A:A=G9),ROUNDUP((COUNTIF(A:A,G9)*(1-0.9)),0)) CA 0.11 FL =LARGE((B:B)*(A:A=G10),ROUNDUP((COUNTIF(A:A,G10)*( 1-0.9)),0)) CA 0.11 GA =LARGE((B:B)*(A:A=G11),ROUNDUP((COUNTIF(A:A,G11)*( 1-0.9)),0)) CA 0.19 IA =LARGE((B:B)*(A:A=G12),ROUNDUP((COUNTIF(A:A,G12)*( 1-0.9)),0)) CA 0.2 ID =LARGE((B:B)*(A:A=G13),ROUNDUP((COUNTIF(A:A,G13)*( 1-0.9)),0)) CA 0.38 IL =LARGE((B:B)*(A:A=G14),ROUNDUP((COUNTIF(A:A,G14)*( 1-0.9)),0)) CO 0.11 IN =LARGE((B:B)*(A:A=G15),ROUNDUP((COUNTIF(A:A,G15)*( 1-0.9)),0)) CO 0.84 KS =LARGE((B:B)*(A:A=G16),ROUNDUP((COUNTIF(A:A,G16)*( 1-0.9)),0)) DE 0.11 KY =LARGE((B:B)*(A:A=G17),ROUNDUP((COUNTIF(A:A,G17)*( 1-0.9)),0)) DE 0.31 LA =LARGE((B:B)*(A:A=G18),ROUNDUP((COUNTIF(A:A,G18)*( 1-0.9)),0)) FL 0.11 MA =LARGE((B:B)*(A:A=G19),ROUNDUP((COUNTIF(A:A,G19)*( 1-0.9)),0)) GA 0.3 MD =LARGE((B:B)*(A:A=G20),ROUNDUP((COUNTIF(A:A,G20)*( 1-0.9)),0)) IL 0.11 ME =LARGE((B:B)*(A:A=G21),ROUNDUP((COUNTIF(A:A,G21)*( 1-0.9)),0)) IL 0.11 MI =LARGE((B:B)*(A:A=G22),ROUNDUP((COUNTIF(A:A,G22)*( 1-0.9)),0)) IL 0.11 MN =LARGE((B:B)*(A:A=G23),ROUNDUP((COUNTIF(A:A,G23)*( 1-0.9)),0)) IL 0.19 MO =LARGE((B:B)*(A:A=G24),ROUNDUP((COUNTIF(A:A,G24)*( 1-0.9)),0)) IL 0.19 MS =LARGE((B:B)*(A:A=G25),ROUNDUP((COUNTIF(A:A,G25)*( 1-0.9)),0)) IL 0.19 MT =LARGE((B:B)*(A:A=G26),ROUNDUP((COUNTIF(A:A,G26)*( 1-0.9)),0)) IL 0.3 NC =LARGE((B:B)*(A:A=G27),ROUNDUP((COUNTIF(A:A,G27)*( 1-0.9)),0)) IL 0.3 ND =LARGE((B:B)*(A:A=G28),ROUNDUP((COUNTIF(A:A,G28)*( 1-0.9)),0)) IL 0.3 NE =LARGE((B:B)*(A:A=G29),ROUNDUP((COUNTIF(A:A,G29)*( 1-0.9)),0)) IL 0.95 NH =LARGE((B:B)*(A:A=G30),ROUNDUP((COUNTIF(A:A,G30)*( 1-0.9)),0)) IL 0.95 NJ =LARGE((B:B)*(A:A=G31),ROUNDUP((COUNTIF(A:A,G31)*( 1-0.9)),0)) IL 0.95 NM =LARGE((B:B)*(A:A=G32),ROUNDUP((COUNTIF(A:A,G32)*( 1-0.9)),0)) IL 1.06 NV =LARGE((B:B)*(A:A=G33),ROUNDUP((COUNTIF(A:A,G33)*( 1-0.9)),0)) IL 1.06 NY =LARGE((B:B)*(A:A=G34),ROUNDUP((COUNTIF(A:A,G34)*( 1-0.9)),0)) IL 1.06 OH =LARGE((B:B)*(A:A=G35),ROUNDUP((COUNTIF(A:A,G35)*( 1-0.9)),0)) IL 1.12 OK =LARGE((B:B)*(A:A=G36),ROUNDUP((COUNTIF(A:A,G36)*( 1-0.9)),0)) IL 1.57 OR =LARGE((B:B)*(A:A=G37),ROUNDUP((COUNTIF(A:A,G37)*( 1-0.9)),0)) IL 2.15 PA =LARGE((B:B)*(A:A=G38),ROUNDUP((COUNTIF(A:A,G38)*( 1-0.9)),0)) IN 0.25 RI =LARGE((B:B)*(A:A=G39),ROUNDUP((COUNTIF(A:A,G39)*( 1-0.9)),0)) IN 2.06 SC =LARGE((B:B)*(A:A=G40),ROUNDUP((COUNTIF(A:A,G40)*( 1-0.9)),0)) IN 3.06 SD =LARGE((B:B)*(A:A=G41),ROUNDUP((COUNTIF(A:A,G41)*( 1-0.9)),0)) MA 1.13 TN =LARGE((B:B)*(A:A=G42),ROUNDUP((COUNTIF(A:A,G42)*( 1-0.9)),0)) MD 0.22 UT =LARGE((B:B)*(A:A=G43),ROUNDUP((COUNTIF(A:A,G43)*( 1-0.9)),0)) MD 0.23 VA =LARGE((B:B)*(A:A=G44),ROUNDUP((COUNTIF(A:A,G44)*( 1-0.9)),0)) ME 1.17 VT =LARGE((B:B)*(A:A=G45),ROUNDUP((COUNTIF(A:A,G45)*( 1-0.9)),0)) ME 3.28 WA =LARGE((B:B)*(A:A=G46),ROUNDUP((COUNTIF(A:A,G46)*( 1-0.9)),0)) MI 0.11 WI =LARGE((B:B)*(A:A=G47),ROUNDUP((COUNTIF(A:A,G47)*( 1-0.9)),0)) MI 0.86 WV =LARGE((B:B)*(A:A=G48),ROUNDUP((COUNTIF(A:A,G48)*( 1-0.9)),0)) MI 1.27 WY =LARGE((B:B)*(A:A=G49),ROUNDUP((COUNTIF(A:A,G49)*( 1-0.9)),0)) Assuming that your result data are all greater than 0. (True) If G is the column where you have the "flag data" and H is the column with your result data (order completion time), (My sample Column A = individual order state result and Column B = individual order completion time) and C1 is the value of the flag data for which you would like the 90 percentile, (Column G G2 = AL, G3 = AR, etc) you may try the following formula in cell D1 I modified the formula to read as below in red as I did not clearly understand your reference values: "=LARGE((B:B)*(A:A=G2),ROUNDUP((COUNTIF(A:A,G2)*( 1-0.9)),0))" Compared to your original below: =LARGE((B:B)*(A:A=C1),ROUNDUP((COUNTIF(A:A,C1)*( 1-0.9)),0)) Note that this is an array formula that has to be entered with CTRL+SHIFT+ENTER rather than just ENTER. If you want you can put the 0.9 in a cell that you refer to instead, but in your original formula it was a constant so I just kept it that way. "Lars-ke Aspelin" wrote: Assuming that your result data are all greater than 0. If G is the column where you have the "flag data" and H is the column with your result data (order completion time), and C1 is the value of the flag data for which you would like the 90 percentil, you may try the following formula in cell D1 =LARGE((B:B)*(A:A=C1),ROUNDUP((COUNTIF(A:A,C1)*(1-0.9)),0)) Note that this is an array formula that has to be entered with CTRL+SHIFT+ENTER rather than just ENTER. If you want you can put the 0.9 in a cell that you refer to instead, but in your original formula it was a constant so I just kept it that way. Hope this helps / Lars-ke On Sun, 7 Sep 2008 08:33:00 -0700, The Hit Man wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple State and product based analysis project
Thanks, I was not aware of the header row limitation with array formulas. The
initial test results appear to be exactly what I expect so your method gets a big two thumbs up! "Lars-Åke Aspelin" wrote: If you use the entire columns in the formula, like B:B, you can not have any (non numerical) headers on row 1. I suggest that you change B:B to B2:B49 and A:A to A2:A49 in two places. Like this: =LARGE((B2:B49)*(A2:A49=G2),ROUNDUP((COUNTIF(A2:A4 9,G2)*(1-0.9)),0)) Adjust the 49 to fit your data size. Don't forget to enter the formula as an array formula. I got reasonable output using your sample date, e.g. IL got 1.67 All other states have less than 10 samples so you just get to top score if you use 90%. By changing 0.9 to 0.5 I got the result 2.06 (the median) for IN as expected. Lars-Åke On Sun, 7 Sep 2008 19:55:02 -0700, The Hit Man wrote: The results were not satisfactory but maybe I am doing something wrong. Order State Order TT Group State 90% TT AL 1.04 AL =LARGE((B:B)*(A:A=G2),ROUNDUP((COUNTIF(A:A,G2)*(1-0.9)),0)) AL 4.07 AR =LARGE((B:B)*(A:A=G3),ROUNDUP((COUNTIF(A:A,G3)*(1-0.9)),0)) AZ 0.11 AZ =LARGE((B:B)*(A:A=G4),ROUNDUP((COUNTIF(A:A,G4)*(1-0.9)),0)) AZ 0.11 CA =LARGE((B:B)*(A:A=G5),ROUNDUP((COUNTIF(A:A,G5)*(1-0.9)),0)) AZ 0.18 CO =LARGE((B:B)*(A:A=G6),ROUNDUP((COUNTIF(A:A,G6)*(1-0.9)),0)) CA 0.11 CT =LARGE((B:B)*(A:A=G7),ROUNDUP((COUNTIF(A:A,G7)*(1-0.9)),0)) CA 0.11 DC =LARGE((B:B)*(A:A=G8),ROUNDUP((COUNTIF(A:A,G8)*(1-0.9)),0)) CA 0.11 DE =LARGE((B:B)*(A:A=G9),ROUNDUP((COUNTIF(A:A,G9)*(1-0.9)),0)) CA 0.11 FL =LARGE((B:B)*(A:A=G10),ROUNDUP((COUNTIF(A:A,G10)*( 1-0.9)),0)) CA 0.11 GA =LARGE((B:B)*(A:A=G11),ROUNDUP((COUNTIF(A:A,G11)*( 1-0.9)),0)) CA 0.19 IA =LARGE((B:B)*(A:A=G12),ROUNDUP((COUNTIF(A:A,G12)*( 1-0.9)),0)) CA 0.2 ID =LARGE((B:B)*(A:A=G13),ROUNDUP((COUNTIF(A:A,G13)*( 1-0.9)),0)) CA 0.38 IL =LARGE((B:B)*(A:A=G14),ROUNDUP((COUNTIF(A:A,G14)*( 1-0.9)),0)) CO 0.11 IN =LARGE((B:B)*(A:A=G15),ROUNDUP((COUNTIF(A:A,G15)*( 1-0.9)),0)) CO 0.84 KS =LARGE((B:B)*(A:A=G16),ROUNDUP((COUNTIF(A:A,G16)*( 1-0.9)),0)) DE 0.11 KY =LARGE((B:B)*(A:A=G17),ROUNDUP((COUNTIF(A:A,G17)*( 1-0.9)),0)) DE 0.31 LA =LARGE((B:B)*(A:A=G18),ROUNDUP((COUNTIF(A:A,G18)*( 1-0.9)),0)) FL 0.11 MA =LARGE((B:B)*(A:A=G19),ROUNDUP((COUNTIF(A:A,G19)*( 1-0.9)),0)) GA 0.3 MD =LARGE((B:B)*(A:A=G20),ROUNDUP((COUNTIF(A:A,G20)*( 1-0.9)),0)) IL 0.11 ME =LARGE((B:B)*(A:A=G21),ROUNDUP((COUNTIF(A:A,G21)*( 1-0.9)),0)) IL 0.11 MI =LARGE((B:B)*(A:A=G22),ROUNDUP((COUNTIF(A:A,G22)*( 1-0.9)),0)) IL 0.11 MN =LARGE((B:B)*(A:A=G23),ROUNDUP((COUNTIF(A:A,G23)*( 1-0.9)),0)) IL 0.19 MO =LARGE((B:B)*(A:A=G24),ROUNDUP((COUNTIF(A:A,G24)*( 1-0.9)),0)) IL 0.19 MS =LARGE((B:B)*(A:A=G25),ROUNDUP((COUNTIF(A:A,G25)*( 1-0.9)),0)) IL 0.19 MT =LARGE((B:B)*(A:A=G26),ROUNDUP((COUNTIF(A:A,G26)*( 1-0.9)),0)) IL 0.3 NC =LARGE((B:B)*(A:A=G27),ROUNDUP((COUNTIF(A:A,G27)*( 1-0.9)),0)) IL 0.3 ND =LARGE((B:B)*(A:A=G28),ROUNDUP((COUNTIF(A:A,G28)*( 1-0.9)),0)) IL 0.3 NE =LARGE((B:B)*(A:A=G29),ROUNDUP((COUNTIF(A:A,G29)*( 1-0.9)),0)) IL 0.95 NH =LARGE((B:B)*(A:A=G30),ROUNDUP((COUNTIF(A:A,G30)*( 1-0.9)),0)) IL 0.95 NJ =LARGE((B:B)*(A:A=G31),ROUNDUP((COUNTIF(A:A,G31)*( 1-0.9)),0)) IL 0.95 NM =LARGE((B:B)*(A:A=G32),ROUNDUP((COUNTIF(A:A,G32)*( 1-0.9)),0)) IL 1.06 NV =LARGE((B:B)*(A:A=G33),ROUNDUP((COUNTIF(A:A,G33)*( 1-0.9)),0)) IL 1.06 NY =LARGE((B:B)*(A:A=G34),ROUNDUP((COUNTIF(A:A,G34)*( 1-0.9)),0)) IL 1.06 OH =LARGE((B:B)*(A:A=G35),ROUNDUP((COUNTIF(A:A,G35)*( 1-0.9)),0)) IL 1.12 OK =LARGE((B:B)*(A:A=G36),ROUNDUP((COUNTIF(A:A,G36)*( 1-0.9)),0)) IL 1.57 OR =LARGE((B:B)*(A:A=G37),ROUNDUP((COUNTIF(A:A,G37)*( 1-0.9)),0)) IL 2.15 PA =LARGE((B:B)*(A:A=G38),ROUNDUP((COUNTIF(A:A,G38)*( 1-0.9)),0)) IN 0.25 RI =LARGE((B:B)*(A:A=G39),ROUNDUP((COUNTIF(A:A,G39)*( 1-0.9)),0)) IN 2.06 SC =LARGE((B:B)*(A:A=G40),ROUNDUP((COUNTIF(A:A,G40)*( 1-0.9)),0)) IN 3.06 SD =LARGE((B:B)*(A:A=G41),ROUNDUP((COUNTIF(A:A,G41)*( 1-0.9)),0)) MA 1.13 TN =LARGE((B:B)*(A:A=G42),ROUNDUP((COUNTIF(A:A,G42)*( 1-0.9)),0)) MD 0.22 UT =LARGE((B:B)*(A:A=G43),ROUNDUP((COUNTIF(A:A,G43)*( 1-0.9)),0)) MD 0.23 VA =LARGE((B:B)*(A:A=G44),ROUNDUP((COUNTIF(A:A,G44)*( 1-0.9)),0)) ME 1.17 VT =LARGE((B:B)*(A:A=G45),ROUNDUP((COUNTIF(A:A,G45)*( 1-0.9)),0)) ME 3.28 WA =LARGE((B:B)*(A:A=G46),ROUNDUP((COUNTIF(A:A,G46)*( 1-0.9)),0)) MI 0.11 WI =LARGE((B:B)*(A:A=G47),ROUNDUP((COUNTIF(A:A,G47)*( 1-0.9)),0)) MI 0.86 WV =LARGE((B:B)*(A:A=G48),ROUNDUP((COUNTIF(A:A,G48)*( 1-0.9)),0)) MI 1.27 WY =LARGE((B:B)*(A:A=G49),ROUNDUP((COUNTIF(A:A,G49)*( 1-0.9)),0)) Assuming that your result data are all greater than 0. (True) If G is the column where you have the "flag data" and H is the column with your result data (order completion time), (My sample Column A = individual order state result and Column B = individual order completion time) and C1 is the value of the flag data for which you would like the 90 percentile, (Column G G2 = AL, G3 = AR, etc) you may try the following formula in cell D1 I modified the formula to read as below in red as I did not clearly understand your reference values: "=LARGE((B:B)*(A:A=G2),ROUNDUP((COUNTIF(A:A,G2)*( 1-0.9)),0))" Compared to your original below: =LARGE((B:B)*(A:A=C1),ROUNDUP((COUNTIF(A:A,C1) *(1-0.9)),0)) Note that this is an array formula that has to be entered with CTRL+SHIFT+ENTER rather than just ENTER. If you want you can put the 0.9 in a cell that you refer to instead, but in your original formula it was a constant so I just kept it that way. "Lars-Åke Aspelin" wrote: Assuming that your result data are all greater than 0. If G is the column where you have the "flag data" and H is the column with your result data (order completion time), and C1 is the value of the flag data for which you would like the 90 percentil, you may try the following formula in cell D1 =LARGE((B:B)*(A:A=C1),ROUNDUP((COUNTIF(A:A,C1)*(1-0.9)),0)) Note that this is an array formula that has to be entered with CTRL+SHIFT+ENTER rather than just ENTER. If you want you can put the 0.9 in a cell that you refer to instead, but in your original formula it was a constant so I just kept it that way. Hope this helps / Lars-Åke On Sun, 7 Sep 2008 08:33:00 -0700, The Hit Man wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sum totals based on product name | Excel Worksheet Functions | |||
| How can I show state-by-state data (as silos) on a map of NA | Charts and Charting in Excel | |||
| Converting State Names to State Abbreviations | Excel Discussion (Misc queries) | |||
| Calculating total value based on another cell's state | Excel Discussion (Misc queries) | |||
| city, state, zip from a single cell to multiple cells | Excel Discussion (Misc queries) | |||
Linear Mode
