Sorting #9

Hi, I have two questions:

- I had data in time like this:

ITEM      T1    T2    T3 ...
item_A    a1    a2    a3 ...
item_B    b1    b2    b3 ...
item_C    c1    c2    c3 ...
item_D    d1    d2    d3 ...
....

and I want to find for every period the average of the 3 minimum values of
items (and the averages should skip negative values and average only
positive).

- Same data, I need to find for every period the items for these 3 minimum
values

:-) I'm sorry if the problem is somehow complicated, but if someone knows
formulas or guide to do this, please tell me.

Svilen


0
9/30/2005 1:02:22 PM
excel 39879 articles. 2 followers. Follow

4 Replies
431 Views

Similar Articles

[PageSpeed] 54

Assuming your data looks like this:
Item	T1	T2	T3	T4
A	0.527	0.291	0.369	0.160
B	0.557	0.182	0.152
C	0.619	0.494	0.565	0.508
D			0.093	0.596
E	0.095	0.207		0.692
F	0.363	0.244	0.095	0.150
G	0.018		0.421	0.057

	0.159	0.211	0.113	0.122
	G	B	D	G
	E	E	F	F
	F	F	B	A

Select the 8 by 5 array and
Insert > Name > Create > Top Row
The average of the 3 smallest values in T1 is 0.159
The underlying formula is
=AVERAGE(SMALL(T1,{1,2,3}))
The matching items are G, E and F and the formulas are
=INDEX(Item,MATCH(SMALL(T1,1),T1,0))
=INDEX(Item,MATCH(SMALL(T1,2),T1,0))
=INDEX(Item,MATCH(SMALL(T1,3),T1,0))
Fill the rest of the array by substituting the proper T()
The blanks in the array are where the negative numbers were.
Name all the original numbers array_a and use the array formula
=IF(array_a>=0,array_a,"")  Shift+Cntrl+Enter to create a second
array and Copy > Paste Special Values back to the first array.

0
10/2/2005 5:13:55 AM
Thank you Herbert! Exatly what i needed!

But I have one more question: In your formulas you use "," as a separator,
but my Excel 2003 SP2 wants ";". Do you have any idea why is that?

Regards,

Svilen

"Herbert Seidenberg" <herbds7-msxls@yahoo.com> wrote in message
news:1128230035.913895.15680@f14g2000cwb.googlegroups.com...
Assuming your data looks like this:
Item T1 T2 T3 T4
A 0.527 0.291 0.369 0.160
B 0.557 0.182 0.152
C 0.619 0.494 0.565 0.508
D 0.093 0.596
E 0.095 0.207 0.692
F 0.363 0.244 0.095 0.150
G 0.018 0.421 0.057

0.159 0.211 0.113 0.122
G B D G
E E F F
F F B A

Select the 8 by 5 array and
Insert > Name > Create > Top Row
The average of the 3 smallest values in T1 is 0.159
The underlying formula is
=AVERAGE(SMALL(T1,{1,2,3}))
The matching items are G, E and F and the formulas are
=INDEX(Item,MATCH(SMALL(T1,1),T1,0))
=INDEX(Item,MATCH(SMALL(T1,2),T1,0))
=INDEX(Item,MATCH(SMALL(T1,3),T1,0))
Fill the rest of the array by substituting the proper T()
The blanks in the array are where the negative numbers were.
Name all the original numbers array_a and use the array formula
=IF(array_a>=0,array_a,"")  Shift+Cntrl+Enter to create a second
array and Copy > Paste Special Values back to the first array.


0
10/3/2005 9:07:37 AM
Excel uses a windows setting--the list separator under Regional settings.

Most in the USA use a comma.  Most in Europe use a semicolon.

Svilen Pachedzhiev wrote:
> 
> Thank you Herbert! Exatly what i needed!
> 
> But I have one more question: In your formulas you use "," as a separator,
> but my Excel 2003 SP2 wants ";". Do you have any idea why is that?
> 
> Regards,
> 
> Svilen
> 
> "Herbert Seidenberg" <herbds7-msxls@yahoo.com> wrote in message
> news:1128230035.913895.15680@f14g2000cwb.googlegroups.com...
> Assuming your data looks like this:
> Item T1 T2 T3 T4
> A 0.527 0.291 0.369 0.160
> B 0.557 0.182 0.152
> C 0.619 0.494 0.565 0.508
> D 0.093 0.596
> E 0.095 0.207 0.692
> F 0.363 0.244 0.095 0.150
> G 0.018 0.421 0.057
> 
> 0.159 0.211 0.113 0.122
> G B D G
> E E F F
> F F B A
> 
> Select the 8 by 5 array and
> Insert > Name > Create > Top Row
> The average of the 3 smallest values in T1 is 0.159
> The underlying formula is
> =AVERAGE(SMALL(T1,{1,2,3}))
> The matching items are G, E and F and the formulas are
> =INDEX(Item,MATCH(SMALL(T1,1),T1,0))
> =INDEX(Item,MATCH(SMALL(T1,2),T1,0))
> =INDEX(Item,MATCH(SMALL(T1,3),T1,0))
> Fill the rest of the array by substituting the proper T()
> The blanks in the array are where the negative numbers were.
> Name all the original numbers array_a and use the array formula
> =IF(array_a>=0,array_a,"")  Shift+Cntrl+Enter to create a second
> array and Copy > Paste Special Values back to the first array.

-- 

Dave Peterson
0
petersod (12004)
10/3/2005 10:13:17 AM
Thanks! I'll check it.

svilen


0
10/3/2005 12:31:23 PM
Reply:

Similar Artilces:

Excel Printing #9
I have an Excel worksheet I want to print continuously on a banner sheet. It is larger than legal size and I can't get this to print without a page break. ...

Sort.....
Hi all, I have data in A column as follows xddlstrpm.p xddostup.p xddpabs.i xddpchk1.i xddpcl.i xddpclmt.p xddpexmt.p xddpflpr.p xddpflprt.p xddpfrmt.p xddpgprp.p xddpilc2.i xddplsc1.i xddplsc2.i xddplsck.i here i wolud like all the extenctions ends with .i to be filled in column B any help.. I don't know if this wil do all you want, but in B1 enter =if(right(A1,1)="i",A1,"") and copy down to the bottton of your data. "kiran" wrote: > Hi all, > I have data in A column as follows > > xddlstrpm.p > xddostup.p > xddpabs.i > xddpchk1....

Sorting & retaining cell format
hi i want to sort a range of A2:D20 by the headers in row 1. however some of the cells in column B have specific formats with different cell colours and a border that diagonally cross the cells in red, to make it looked crossed out. when i set up the sorts they work OK but the border formatting stays with the original cell location rather than moving with the correct text. does anyone have any ideas? Nick Hi Nick AFAIK the only workaround would be using conditional format based on your cell values. Fixed border formats will not be sorted. Frank Nick wrote: > hi > > i want...

Special Pasting Quattro Pro 9 file as bitmap, how to manipulate now?
The only way I could find to get a Quattro Pro 9 file into Excel 2002, with gridlines and the exact diagram (property and house layout), was to copy and paste special it as a bitmap. Now that it's in Excel, I'd like to change some things in the diagram but the cursor, when over the copied part, shows as a 4 sided arrow (a compass rose with 4 arrows pointing to the N,S,E, and W.) and the actual cursor arrow attached to and on the SE part of this 4-sided arrow. This 4 sided arrow cursor can only move the copied part and nothing else . When I move the cursor off the copied part, it reve...

Sorting data in group
Working on a excel sheet maintaining huge data and need to sort it date wise, amount wise (range 1 to 10000, 10001 to 20000). The problem is that we have data set of different clients, this data set of each client spread over 9 columb and 7 rows and i need to sort it in a group. How do i do this? Data set should'nt be destroy? for example Columbs are: Name of Client nature of business funded non funded trade business date of approval limit expiry date fresh/renewal remarks. rows are: DM MMF IERF BD/RISK LINE PAD OTHERS SUBTOTAL See your post in ...

filter and sorting
I am trying to perform a ascending sort and keep getting an error ... this operation requires the merged cells to be identically sized. I have a table 3 top rows are the header with various merged cells, then I have a variable number of data rows (no merged cells), and finally there are several footer row with various merged cells. I am needing to sort the data rows, how can I do this? QB If you carefully select the data rows range between your upper and lower merged cells you can sort that range. Best to try to do without merged cells which cause these problems in the fi...

Select 'Sort By' column by simply typing heading letter
Hello, In the next version of MS Excel, please restore the ability to define Sort By columns by simply typing the Column heading letter as was the case in Word 2003. When Excel 2007 came out, I was astonished to discover that capability was no longer available. For example, suppose I want to sort a large spreadsheet by Column CDX. In both Excel 2003 and 2007, if I open the Sort box and click Sort By, the drop down menu, by default, lists Column A as the first option. In Excel 2003, I could simply click twice to insert the cursor, type backspace (to remove the A), then ...

import- number- sort?
I imported this data into a spread sheet- I tried to format it to number, but when I sort it- I get : 37.08 4.33 4.79 4.8 42.56 5.94 Whats up with this? Thanks Brent Hi Brent, Install the TrimAll macro, select the column and invoke the macro from Alt+F8 http://www.mvps.org/dmcritchie/excel/join.htm#trimall Directions to install and use a macro at http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro I expect that you have spaces or CHAR(128) "Required Blank" characters in you data you can check is =LEN(A1) --- HTH, David...

Sorting attributes in Excel?
i am wondering how to sort attributes in excel? i want to have a first attribute which i keep constant, and then have a list of second attributes which i want to sort. any help would be appreciated! Hi not really sure what you're trying to achive. Maybe provide an example! "addie" wrote: > i am wondering how to sort attributes in excel? i want to have a first > attribute which i keep constant, and then have a list of second attributes > which i want to sort. any help would be appreciated! ...

Combined & Sorted Text Fields
I have 12 columns of Letters that need to be combined into one cell in the correct alphabetical order. Eg. Column 1 may have C whilst column 2 has B and so on. I need the last column to sort the letters alphabetically so it would be simply BC in the last cell. Any ideas? -- andyholland ------------------------------------------------------------------------ andyholland's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26491 View this thread: http://www.excelforum.com/showthread.php?threadid=397574 You could select the range containing your data and the sort a...

Group Items on Report but do Not Sort the Group alphabetically
I have a table called "category" with a list of dropdown items in it. The table only has an autonumber key and the list of items in it. The items are listed in a specific order in the table and are not sorted alphbetically in the table nor in the query for the table. The items are listed in the table as a dropdown in this order and the query also shows the items in this order: Administration Human Resources Events Periodicals I want to group the data in my report based on the "category" listing as shown in both the table and the query. However, as soon as "group&...

Smartlist Builder since implementing 9.0
Now that we started using 9.0 smartlists made in smartlist builder are not automatically added to the smartlist menu in GP. We do get the message that when opening the smartlist menu that smartlist has detected changes, etc. Should there have been a specific smartlist update as well when we went to 9.0? With 9.0, you no longer have security access to the new smartlists by default. You have to go to Smartlist security and grant access to the new object. While it's a bit of a hassle, the goal is to make sure that not everyone has access to your new Payroll Employee Salary (or whatever)...

Sort Question
Hello, I have a Combo Box as a Group By item in my Access 2007 report. The grouping is correct, however the sorting appers to take place based on the ID Column(0) of the Combo Box insted of actual text Column(1). How can I force the report to sort by the value and not the ID? Thank you, Silvio Your report's record source must contain the table and field with "the value" so you can use it to sort. This seems to be another reason why lookup fields in tables are a bad idea. -- Duane Hookom Microsoft Access MVP "Silvio" wrote: > Hello,...

sorting numbers and numbers that contain text in excel
A column contains both strictly numbers and also numbers that are followed by text (e.g., row 1: 1000, row 2: 1500; row 3: 1000a; row 4: 1500a) Identical numbers are related documents, with the text suffixes referring to addenda documents; thus, document 1000 has an addendum document 1000a; How can I sort the column so in the following order: row 1 (1000), row 3 (1000a), row 2 (1500), row 4 (1500a)? Thank you -- MZ =TEXT(A1,"0") will turn each into text, then sort by that helper column (and don't accept Excel's suggestion to treat text that looks like number...

random #'s for a population how do you sort w/o changing #'s
How do you use the sort filter smallest to largest? When I try I see that the numbers are changing not just reordering? Any ideas? Anna, As you correctly observe the numbers change because the sort causes the worrksheet to recalculate. There are a number of ways around it. One suggestion is copy your random numbers and paste special/paste values into another range and then sort this range -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Anna&qu...

Sort by Doc Date
In the inquiry windows such as Vendor Transaction Inquiry, it would be nice to have the option to sort by the document date or by the voucher number. -- Elisabeth ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/News...

Customer Relationship
CRM 3.0 If a user opens up the Account or Contact entity, the Relatinoships icon in the left menu is available. When clicking the Relationships icon, a view of existing Customer Relationships is displayed. Our implementation shows 4 columns (as I believe everyones' does): Party 1, Role 1, Role 2, Party 2. A while back, users had the ability to click on those column headers to sort the records how they wanted. But for some reason, the application today has no default sort order and does not allow the selection of a column header at all. The only thing one of our end users could...

GP 9 Upgrade and SQL Server 2000 sp4
per microsoft's documentation sql server sp4 must be installed before upgrading to GP 9. does anyone know if the sql server sp 4 install is necessary for the GP 9 upgrade? microsoft's documentation indicated that all service packs for GP 8 must be installed before upgrading to GP 9, but after speaking with support, they said the GP 8 service packs aren't necessary. bottom line: i'd like to avoid the sql sp4 install if necessary as i had to rebuild my test server after installing sp4 last week. thank you. Since the database requirements for GP 9.0 list SQL 2000 SP 4 a...

Sorting Aplhanumeric Text as Numeric Values in Excel 2003
I was able find information on this for Excel 97, but could not find anything that looked at the subject in Excel 2003. Any help would be appreciated. ...

Why can't save SORTS?
An using MS Office 2002 Excel. Have various worksheets that I always sort a certain way... for instance, a list sorted by Column B, then Column A, then Column C...(with no header row). Problem is... everytime I go to sort, I have to RE-ENTER the B, A, C sort criteria. Is there a way to save it for a specific worksheet file? Have had some suggestions to write macros, but this takes time, and it seems like surely there is a way to retain the last sort entered. Lotus 123 saves your last sort criteria with the spreadsheet. Alternatively, does MS Office *2003* Excel correct this? Would appre...

3M SandBlaster 423-120 Flexible Sanding Pad, 3.7 x 9.25-Inch, 120-Grit, Rectangle, 2-Sheet
Price:$7.30 Image: http://discountadvisors.info/image.php?id=B000GLHW3Q Best deal: http://discountadvisors.info/index.php?id=B000GLHW3Q Sandblaster, 2 Pack, 120G 3.7" x 9" Large Flexible Abrasives Detailed Sandpaper, Lasts 10 Times Longer Than Normal Sandpaper, Extreme Durability, Ultimate Comfort, Sandpaper Can Also Be Used On Detailed Flexible Abrasives Tool True Value #630-485. SIMILAR PRODUCTS: 3M SandBlaster 423-180 Flexible Sanding Pad, 3.7 x 9.25-Inch, 180-Grit, Rectangle, 2-Sheet:http://discountadvisors.info/index.php?id=B000GLE1OO 3M SandBlaster 413-220 Flexib...

Integration Manager 9.0 Service Packs on Vista
I have installed Dynamics GP on a new PC running Vista, but cannot get Integration Manager to work. I was able to install the 9.0 version from the CD, but I cannot get SP1 or SP3 to install. At first the SP installs would not recognize the .msi file that it wanted to access from the CD. I finally copied the CD's .msi file to the Desktop and got it past that step. Now I get what must be the latest generation of MS error messages, a mostly white window with the Title "Error", a red X icon with the words "Unexpected error; quitting". The only option is to click...

Sorting Values #2
I would like to sort out a list of options. You can see me example at http://spreadsheets.google.com/ccc?key=rtwCZLHXFwrJu-gj88zwQPg I don't know if this should be a function in the sheet or a macro. Pretty much want to look at and CONCATENATE the 1st 3 columns and drop out any duplicates. May results should look like cloumns G & H Diana http://people.tribe.net/littleredhairedgirl littleredhairedgirl wrote: > I would like to sort out a list of options. You can see me example at > http://spreadsheets.google.com/ccc?key=rtwCZLHXFwrJu-gj88zwQPg > > I don't know if thi...

sorting #14
This is pretty basic, I guess, but I'm looking for the proper way to sort by any column and have the data stay related. If I create a range of A:1-D:4 like this: NOW IS THE TIME FOR ALL GOOD MEN TO COME TO THE AID OF THEIR PARTY and sort on the first column, it becomes: AID IS THE TIME FOR ALL GOOD MEN NOW COME TO THE TO OF THEIR PARTY and of course, I just ruined my data! I want each column enrty of a given line to stay related (as though each line was a record in a database, and each column was a field. Of course, by selecting the whole range and sorting, it will do it correctly. ...

Can sort Emoticons?
I have noticed that when you add emoticons, they show up at the end of the pre existing ones but when you restart MSN, your emoticons are not sorted by any understandable criteria making almost impossible to find an emoticon you want. I have over 500 custom emoticons and even when I try to use some easy namings, 500 is a huge number to remember them all. Is there any way to sort them by name or description? Is there any way to export or print out a guide with an icon sample and keys to activate each one? Thanks in advance. "El Arcangel" <elarcangelgabriel@hotmail.com&...