Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 13th 08, 02:00 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 142
Default 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   Report Post  
Old 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
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula for list summary Sinner Excel Discussion (Misc queries) 2 February 14th 08 01:22 PM
Creating A Breakout List from a Summary List? cardan Excel Programming 1 September 7th 06 12:41 AM
Creating a Detailed List from a Summary List [email protected] Excel Worksheet Functions 0 September 7th 06 12:36 AM
Creating A Breakout List from a Summary List [email protected] Excel Discussion (Misc queries) 0 September 6th 06 11:58 PM
Pulling a Summary List from a Larger List 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.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017