Remember Me?

#1
February 13th 08, 02:00 PM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Mar 2007 Posts: 142
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
February 13th 08, 02:54 PM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 5,651
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Sinner Excel Discussion (Misc queries) 2 February 14th 08 01:22 PM cardan Excel Programming 1 September 7th 06 12:41 AM [email protected] Excel Worksheet Functions 0 September 7th 06 12:36 AM [email protected] Excel Discussion (Misc queries) 0 September 6th 06 11:58 PM Stephen - Dallas Excel Discussion (Misc queries) 2 May 3rd 06 02:51 PM

All times are GMT +1. The time now is 06:07 AM.