Transpose wont work with Small(IF) formula

[FrederikBjerre]

Hi,

I have a huge matrix (500*500) with value differences of 500 companies between eachother.
I now want to find the 10 companies with the closests/smallest value difference for each company.

I have therefor made and formula {=SMALL(IF(C3:SH3>0;C3:SH3);{1,2,3,4,5,6,7,8,9,10})} to find the 10 smallest value differences for company A (row C3:SH3), but at the same time ignore blanks or zeroes.

However this formula wont transpose for the following reasons:

1) The I transpose it to cower 10x500 rows the cell reference to C3:SH3 wont change to C4:SH4 and so on... Why?
2) The k-value (to find the 10 closest values) changes from {1,2,3,4,5,6,7,8,9,10} to {1,234567891}. Why?

Can someone help?

Thansk in advance!

Fred

[NBVC]

I think the row separator for your regional settings is a / rather than a comma?

i.e. {1/2/3/4/5/6/7/8/9/10}

I am not sure though exactly what you are trying to accomplish... can you post a small sample workbook, emulating what you need done?