return nth smallest value from data set

[tommu]

Hello,
I have a simple data set in sheet 1, where in Row 10, every 6th column is populated with a value (in between these values is a combination of numbers and blank cells)

I am trying to use the SMALL function to pull back the smallest 1 through to 5.

I have tried to combination of offset(sheet1!$A$10,(column()-1)*6,0)

but this doesn't work

any help would be greatly appreciated

 

[6StringJazzer]

So you just want to consider the numbers in columns A, F, L, R, etc.? Up until what column? What version of Excel do you have (consider updating your profile)?

 

[tommu]

Sorry, I have officer 365

you are correct in the values. To add more.

Sheet 1 has data from A1:AA50.

In Sheet 2 a1 - I want to show the smallest number from sheet1, row 10 - but only looking at every 6th column (so smallest from A10, F10, L10...).
Sheet2!A2 - I want to show the second smallest number from the same data set A10, F10, L10...)
A3 - the 3rd smallest etc.

 

[bstory84]

Hi @tommu
Give this a try in sheet2 cell A1 then copy and paste down.

=SMALL(IF((MOD(COLUMN(Sheet1!$10:$10),6)=0)*(Sheet1!$10:$10>0),Sheet1!$10:$10),ROW())

 

[Peter_SSs]

I have officer 365

Please put that in your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

only looking at every 6th column (so smallest from A10, F10, L10...)

That does not clarify the requirement as those columns are not evenly spaced.
There are 4 columns between A and F but 5 columns between F and L.

Assuming that the columns of interest are uniformly spaced and the first column of interest is column A, then try this formula in A1 of Sheet2 and no need to copy it anywhere. Change the number after "every" in the formula if it is not every 6 columns that you want.

=LET(r,Sheet1!A10:AA10,every,6,INDEX(SORT(FILTER(r,MOD(COLUMN(r)-COLUMN(INDEX(r,1)),every)=0),,,1),SEQUENCE(5)))