Search highest numbers in a row

Hi guys,

I searched this forum a while and didn't really found what I was looking for.

I want to extract the highest numbers from a row, but with certain conditions.
KW01_stats receiving.xlsx

In cell 'P35' I want the first value from row 33 which is higher than 20
In cell 'P36' I want the second value from row 33 which is higher than 20
In cell 'P37' I want the third value from row 33 which is higher than 20
In cell 'P38' I want the fourth value from row 33 which is higher than 20

I tried it with the formula "Large", but this f.e. only gives me the third high ranked number, not the third high positioned number. If you know what I mean..?

Can someone help me out with this one?

Thanks

The largest number on row 33 is 17 (try it with =MAX(33:33) ), so none of them match your criteria.

Pete

Sorry forgot to say: look in the sheet "test"

Thanks

I can't see a sheet called "test" - stop messing us about.

Pete

KW01_stats receiving.xlsx
Sorry

So just to recap, you don't want the highest number first - instead, you want the first number across the columns which exceed 20 (i.e. 2232, which occurs in column BYS), then the next number would be 256, which occurs in column CCS, then 359 in column CKJ, and so on?

Pete

Hi pete,
that's exactly what i'd want.
Thanks

Put this array* formula in cell P35:

=IFERROR(INDEX($33:$33,SMALL(IF($B$33:$CLW$33>20,COLUMN($B$33:$CLW$33)),ROWS($1:1))),"")

*Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar, but you should not type these yourself. If you need to edit or amend the formula, you must use CSE again to commit it.

You can then copy the formula down into P36:P39 using the normal method(s).

Hope this helps.

Pete

I'll try it on monday when I get back at work.
I'll keep you informed.

Thanks in advance
Regards

Hi,

I tried the formula, but I keep getting error messages. I also used CSE like you told.
=IFERROR(INDEX($33:$33,SMALL(IF($B$33:$CLW$33>20,COLUMN($B$33:$CLW$33)),ROWS($1:1))),"")

Do I need to adjust something if I put this in another cell?

Grtz

What error messages are you getting? Do you see the curly brackets around the formula in the formula bar? I tried it out in cell W35, so it whould work elsewhere.

Attach the workbook with the formula in if you can't get it to work.

Hope this helps.

Pete

Hi Pete,

This is what I entered
formula failure 1.png

This is the failure I get
formula failure 2.png

I just copied your formula into the formulabar and committed wih CSE.

Is my office version different from yours? I use 2013

Grtz

I can't view PNG files on this forum - please attach an Excel workbook instead.

Pete

I can't save my excel worksheet it there is failure in the formula.
I slightly adjusted the formula into this:
=IFERROR(INDEX(33:33(IF($B$33:$CLW$33>20;COLUMN($B$33:$CLW$33));ROWS($P35:$P38))),"")
I changed the columns into semi-columns and changed $1:1 into $P35:$P38. When I commit this I get the notification that there aren't enough arguments for this formula.
The workbook in which I tried it is still the same as the one I uploaded

Grtz

I've put the formula in cell P35 of the file you attached to Post #5, and then copied it down.

Hope this helps.

Pete

Now it works fine. Thanks!

I noticed that if I copy the formula into another sheet, I have to save the file before I see the results..

Thanks again

Grtz

Perhaps the file is set for Manual recalculation - click on Formulas | Calculation Options and ensure that Automatic is checked.

Thanks for the rep - if that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete