A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Summarize range



 
 
Thread Tools Display Modes
  #1  
Old May 16th, 2009, 04:38 PM posted to microsoft.public.excel.worksheet.functions
willemeulen[_10_]
external usenet poster
 
Posts: 1
Default Summarize range


I want to make an automatic summary of value's present in a certain
column in ascending order.

Example:

Column
8
10
12
10
8
16
16
6


Result:

6 8 10 12 16

In my case the results should be displayed vertical, one value per
column. I know what the maximum amount of different values can be (it
will never exceed 10 different values) and the column which needs to be
filtered is also fixed.

Thanks,

W


--
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=97296

  #2  
Old May 16th, 2009, 04:57 PM posted to microsoft.public.excel.worksheet.functions
Domenic[_2_]
external usenet poster
 
Posts: 265
Default Summarize range

Assuming that A2:A9 contains the data, try the following...

1) To return the results in a vertical range of cells...

C2, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(FREQUENCY(IF(A2:A9"",MATCH("~"&A2:A9,A2: A9&"",0)),ROW(A2:A9)-RO
W(A2)+1),1))

D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(D$22)=$C$2,SMALL(IF(FREQUENCY(IF($A$2: $A$9"",MATCH("~"&$A$2
:$A$9,$A$2:$A$9&"",0)),ROW($A$2:$A$9)-ROW($A$2)+1),$A$2:$A$9),ROWS(D$22
)),"")

2) To return the results in a horizontal range of cells...

C2, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(FREQUENCY(IF(A2:A9"",MATCH("~"&A2:A9,A2: A9&"",0)),ROW(A2:A9)-RO
W(A2)+1),1))

D2, confirmed with CONTROL+SHIFT+ENTER, and copied across:

=IF(COLUMNS($D22)=$C$2,SMALL(IF(FREQUENCY(IF($A $2:$A$9"",MATCH("~"&$
A$2:$A$9,$A$2:$A$9&"",0)),ROW($A$2:$A$9)-ROW($A$2)+1),$A$2:$A$9),COLUMNS(
$D22)),"")

--
Domenic
http://www.xl-central.com

In article ,
willemeulen wrote:

I want to make an automatic summary of value's present in a certain
column in ascending order.

Example:

Column
8
10
12
10
8
16
16
6


Result:

6 8 10 12 16

In my case the results should be displayed vertical, one value per
column. I know what the maximum amount of different values can be (it
will never exceed 10 different values) and the column which needs to be
filtered is also fixed.

Thanks,

W

  #3  
Old May 16th, 2009, 06:22 PM posted to microsoft.public.excel.worksheet.functions
Teethless mama
external usenet poster
 
Posts: 3,722
Default Summarize range

In B1: =SMALL(Data,1)
In C1: =IF(MIN(IF(DataB1,Data))=0,"",MIN(IF(DataB1,Data )))
ctrl+shift+enter, not just enter. Copy C1 across to other columns



"willemeulen" wrote:


I want to make an automatic summary of value's present in a certain
column in ascending order.

Example:

Column
8
10
12
10
8
16
16
6


Result:

6 8 10 12 16

In my case the results should be displayed vertical, one value per
column. I know what the maximum amount of different values can be (it
will never exceed 10 different values) and the column which needs to be
filtered is also fixed.

Thanks,

W


--
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=97296


  #4  
Old May 16th, 2009, 07:17 PM posted to microsoft.public.excel.worksheet.functions
willemeulen[_11_]
external usenet poster
 
Posts: 1
Default Summarize range


This Ctrl+Shift+Enter thing, array or something they call it. I read in
some of the forums you should avoid it when possible as it has some
disadvantages.

Once done does it work or should the user repeat it after the sheet has
been modified? I would like to avoid that as I'm writing the sheet for
other people to use, and they are complete with Excel.


--
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=97296

  #5  
Old May 16th, 2009, 08:11 PM posted to microsoft.public.excel.worksheet.functions
Domenic[_2_]
external usenet poster
 
Posts: 265
Default Summarize range

In article ,
willemeulen wrote:

This Ctrl+Shift+Enter thing, array or something they call it. I read in
some of the forums you should avoid it when possible as it has some
disadvantages.


In general, array formulas, which need to be confirmed with
CONTROL+SHIFT+ENTER, take longer to calculate. If the range is
relatively small and the worksheet doesn't contain many of them, then it
likely won't be an issue. However, if the range is large and the
formula is copied to many cells, it can really slow down the worksheet.

Once done does it work or should the user repeat it after the sheet has
been modified?


The only time an array formula needs to be re-confirmed with CSE is
after modifying the formula itself.

I would like to avoid that as I'm writing the sheet for
other people to use, and they are complete with Excel.


Assuming that A2:A9 contains the data, try...

C2:

=SMALL(A2:A9,1)

C3, copied down until the formula returns #NUM!:

=SMALL($A$2:$A$9,COUNTIF($A$2:$A$9,"="&C2)+1)

--
Domenic
http://www.xl-central.com
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:17 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.