Sorting

[Svilen Pachedzhiev]

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

 

[Herbert Seidenberg]

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.

 

[Svilen Pachedzhiev]

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

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]

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]

Thanks! I'll check it.

svilen