Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a formula
Hello,
I'm seeking ideas or solutions to overcome the manual processes of developing an analysis. We have a spreadsheet containing the list of production results by state by company and would like to filter this list by Top 20%, Middle 60%, & Bottom 20% along with their total production results based on # of company per that state. For example: AK has 27 companies - 5 @ Top 20%, 17 @ Middle 60%, & 5 @ Bottom 20% Is there a way to set up a formula to use the table above and recognize that there are 5 companies belong to Top 20% and SUM the "Production" of Top 20% (first 5 companies) automatically? Then, apply this methodology to Middle 60% and Bottom 20%. ID Agency Name (Formatted) State Production* 1 Ribelin Lowell & Company Ins AK 362 2 Insurancemart Inc AK 305 3 Acordia Of Alaska Inc AK 243 4 Robert Cederholm & Assoc. Inc. AK 221 5 Randall Moss Insurance Inc AK 183 6 Pippel Insurance Agency Inc AK 180 7 Homer Insurance Center Inc AK 134 8 Kenneth A Murray Insurance Inc AK 132 9 Davies-barry Insurance AK 113 10 Aaa Mountainwest Inc AK 98 11 Walters & Associates Ins Inc AK 93 12 Petersburg Insurance Center AK 77 13 J C Morris Agency Inc AK 55 14 Rural Alaska Ins Agency Inc AK 51 15 Venneberg Insurance, Inc. AK 51 16 Brown Agency AK 50 17 Alaska Pacific Insurance Llc AK 46 18 Hagen Insurance AK 46 19 Denali Alaskan Insurance Llc AK 45 20 Integrity Ins Agency Llc AK 43 21 Shattuck & Grummett, Inc. AK 43 22 Luke & Company Ins Services AK 39 23 S & H Inc AK 38 24 Chi Of Alaska Inc AK 26 25 Alaska Service Agency Inc AK 21 26 Gwaltney & Associates Inc AK 9 27 Insurance Group Of Alaska Llc AK 0 * It was sorted by Production results Thank you in advance! David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a formula
One way:
J1&J2 array-entered (CTRL-SHIFT-ENTER or CMD-RETURN), your production values named "rng": J1: =SUM(LARGE(rng,ROW(INDIRECT("1:" & INT(COUNT(rng)/5))))) J2: =SUM(SMALL(rng,ROW(INDIRECT("1:" & INT(COUNT(rng)/5))))) J3: =SUM(rng,-J1,-J2) In article , "David" wrote: Hello, I'm seeking ideas or solutions to overcome the manual processes of developing an analysis. We have a spreadsheet containing the list of production results by state by company and would like to filter this list by Top 20%, Middle 60%, & Bottom 20% along with their total production results based on # of company per that state. For example: AK has 27 companies - 5 @ Top 20%, 17 @ Middle 60%, & 5 @ Bottom 20% Is there a way to set up a formula to use the table above and recognize that there are 5 companies belong to Top 20% and SUM the "Production" of Top 20% (first 5 companies) automatically? Then, apply this methodology to Middle 60% and Bottom 20%. ID Agency Name (Formatted) State Production* 1 Ribelin Lowell & Company Ins AK 362 2 Insurancemart Inc AK 305 3 Acordia Of Alaska Inc AK 243 4 Robert Cederholm & Assoc. Inc. AK 221 5 Randall Moss Insurance Inc AK 183 6 Pippel Insurance Agency Inc AK 180 7 Homer Insurance Center Inc AK 134 8 Kenneth A Murray Insurance Inc AK 132 9 Davies-barry Insurance AK 113 10 Aaa Mountainwest Inc AK 98 11 Walters & Associates Ins Inc AK 93 12 Petersburg Insurance Center AK 77 13 J C Morris Agency Inc AK 55 14 Rural Alaska Ins Agency Inc AK 51 15 Venneberg Insurance, Inc. AK 51 16 Brown Agency AK 50 17 Alaska Pacific Insurance Llc AK 46 18 Hagen Insurance AK 46 19 Denali Alaskan Insurance Llc AK 45 20 Integrity Ins Agency Llc AK 43 21 Shattuck & Grummett, Inc. AK 43 22 Luke & Company Ins Services AK 39 23 S & H Inc AK 38 24 Chi Of Alaska Inc AK 26 25 Alaska Service Agency Inc AK 21 26 Gwaltney & Associates Inc AK 9 27 Insurance Group Of Alaska Llc AK 0 * It was sorted by Production results Thank you in advance! David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |