address of SMALL function result

[Guest]

I need to use a function that gives me the lowest value, and the K lowest value from a bunch of cells/values

However, getting the k lowest value is not enough. There are other values next to the k lowest value that I need to retrieve. So I need the address or location or column of that value to get them

Is there anyway to get the address of a function result value

Thanks

MT

 

[Frank Kabel]

Hi MT
if column A stores your lookup values and column B the value you want
to retrieve try
=INDEX(B1:B1000,MATCH(SMALL(A1:A1000,k),A1:A1000,0),1)
Note: If you have two identical values this will return only the first
occurence

Frank

 

[Guest]

Thanks Frank

Thats what I used the last time I did this project. But the formulas got so long. And there were also duplicate values that caused problems

I was hoping for a solution that I didnt find

Thanks

MT

 

[Frank Kabel]

Thanks Frank.

Thats what I used the last time I did this project. But the formulas
got so long. And there were also duplicate values that caused
problems.

I was hoping for a solution that I didnt find.

Thanks,

MT

Hi
for duplicates may be the following will help you (has to be entered as
array formula):
=INDEX(B1:B1000,MATCH(SMALL(A1:A1000+ROW(A1:A1000)/10000,k),A1:A1000+RO
W(A1:A1000)/10000,0),1)

This adds a small numbers based on the row position for each value (the
number has to be smaller than the smallest difference between your
numbers. So I divide the row number by 10,000)
This solves your duplicates problem. For the long formulas I would
suggest that you create a defined name for your range and use this
instead. e.g.
=INDEX(rng_2,MATCH(SMALL(rng_1+ROW(rng_1)/10000,k),A1:A1000+ROW(rng_1)/
10000,0),1)

HTH
Frank

 

[Guest]

Thanks a lot

Regarding the named range, the numbers that are being searched for Small are in about 10 columns. Each column is separated by 3 other columns of other data. And there are about 800 rows of data
Is there a way to do a named range of separate columns, that will not refer to a certain row, but each row separately

Im also wondering if I should use Access for this

Right now I am using VBA subs to do all this work because the lasdt time I did this, I did it with cell formulas, and they got so long I couldnt keep track. I had some formulas with 25 nested IF/Then statements
Now I just have the VBA sub read the values into an array, analyze, do math on the and spit them out
Its more logical and easier to debug, but doesnt have the speed and real time updating that built in Excel formulas do
I also tried doing custom cell functions. It took a long time for these to compute to 16 colums x 800 rows
And everytime another related cell was read by my VBA code, they would all update again, making the debugging process painfully slow

The problem with doing it the way that I am with VBA subs (not custom cell functions) is that its a one-time write operation, and it doesnt update or refresh unless I run the sub again

Ive got to find a better way for the next time

Maybe import all the data to Access and use Access VBA

MT

 

[Harlan Grove]

Regarding the named range, the numbers that are being searched
for Small are in about 10 columns. Each column is separated by
3 other columns of other data. And there are about 800 rows of
data. Is there a way to do a named range of separate columns,
that will not refer to a certain row, but each row separately?

So you're searching through each row separately? Or are you searching for
the k_th smallest value in all 10 columns and all 800 rows? If the latter,
how would you deal with a tie for the k_th smallest value in the 600th row
of the 3rd column and the 50th row of the 7th column? In other words, would
you search by rows then columns or by columns then rows?

Im also wondering if I should use Access for this.

Not a good idea. Finding anything other than the largest and smallest values
isn't easy in Access.