Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula for list summary
I have a list below with min & max values. I want a summary in such a way that the formula calculates the first number in range & last number and continue for any skiped series. List 12220 12221 12222 12223 12224 12225 12226 12227 133356 133357 133359 133360 133361 133362 133363 133364 ________________________ Result: Start End Quantity 12220 12227 8 133356 133357 2 133359 133364 6 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula for list summary
On Wed, 13 Feb 2008 05:00:56 -0800 (PST), Sinner wrote:
I have a list below with min & max values. I want a summary in such a way that the formula calculates the first number in range & last number and continue for any skiped series. List 12220 12221 12222 12223 12224 12225 12226 12227 133356 133357 133359 133360 133361 133362 133363 133364 ________________________ Result: Start End Quantity 12220 12227 8 133356 133357 2 133359 133364 6 Assumptions: List is a NAME'd range D1: Start E1: End F1: Quantity D2: =List *E2: =INDEX(List,MATCH(FALSE,OFFSET(List,0,0)+1=OFFSET( List,1,0),0)) F2: =COUNTIF(List,"="&D2)-COUNTIF(List,""&E2) *D3: =INDEX(List,MATCH(E2,List,0)+1) *E3: =INDEX(OFFSET(List,MATCH(D3,List,0)-1,0),MATCH(FALSE,OFFSET( List,MATCH(D3,List,0)-1,0)+1=OFFSET(List,MATCH(D3,List,0),0),0)) F3: =COUNTIF(List,"="&D3)-COUNTIF(List,""&E3) * -- ARRAY ENTERED FORMULAS (Confirm with <ctrl<shift<enter. Excel will place braces {...} around the formulas) Then select D3:F3 and fill down as far as required. You will get errors when you have filled down too far. You can test for an error in various ways to suppress the output, if necessary. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for list summary | Excel Discussion (Misc queries) | |||
Creating A Breakout List from a Summary List? | Excel Programming | |||
Creating a Detailed List from a Summary List | Excel Worksheet Functions | |||
Creating A Breakout List from a Summary List | Excel Discussion (Misc queries) | |||
Pulling a Summary List from a Larger List | Excel Discussion (Misc queries) |