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

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