Identify First number in row that is less than or equal to x

  • Does anyone know a good formula for identifying the first value in a row that is less or equal to than another?


    For example, if I have the row:


    35 34.2 33.9 33.8 33.9 33.6 33.8 33.9 34 35 36 37


    I would like to identify the first number from the left that is less than or equal to 34 i.e. 33.9 in position 3.


    Thanks

  • Re: Identify First number in row that is less than or equal to x


    I thought I could use =MAX(A1:L1,COUNTIF(A1:L1,"<=34")) to give me the highest value from the left hand side that is less than or equal to 34 but this doesn't seem to be working.

  • Re: Identify First number in row that is less than or equal to x


    Thanks,


    That works well, but the only thing is that the '=' overrides the '<', so that when I apply it to the above formula it gives me 34 (in position 9) rather than 33.9 (in position 3). I've tried adding " " and ( ) around the <=34 to isolate it, but to no avail. Any suggestions?

  • Re: Identify First number in row that is less than or equal to x


    I want the first number in the row that is less than or equal to 34. =LARGE((A1:L1<=34)*(A1:L1),1) gives me the first number equal to 34 (i.e. it ignores the <) also I just realised that this function returns the largest number less than 34, not the first number less that 34.


  • Re: Identify First number in row that is less than or equal to x


    I think the second function is better, it still has the same problem of ignoring the < as soon as I put in = i.e. =INDEX(MAX((A1:L1<=34)*(A1:L1)),1)

  • Re: Identify First number in row that is less than or equal to x


    Only I need it to identify the first number from the left that is less than 34. i.e. the 33.9 in the srd column, not the 33.9 in the 8th column...

  • Re: Identify First number in row that is less than or equal to x


    Great thanks, that works well. Can the ADDRESS function just be applied to this to find the coordinates of the cell? I tried =ADDRESS(INDEX(A1:L2,MATCH(TRUE,A1:L1<=34,0))),A1:L1,4) but there are 'not enough arguments'?

  • Re: Identify First number in row that is less than or equal to x


    I also tried =ADDRESS(ROW(INDEX(B2:AR2,MATCH(TRUE,B2:AR2<=34,0))),COLUMN(B2:AR2),4) with control + Shift + Enter, this results in the row, but does not give the correct column. i.e. it just gives A1, A2, A3, A4 etc

  • Re: Identify First number in row that is less than or equal to x


    Yes the first number less than 34. This function was able to do it: =INDEX(A1:L1,MATCH(TRUE,A1:L1<=34,0)) which gives the numerical value, so now I just need to find the coordinates for the numerical value I've found

  • Re: Identify First number in row that is less than or equal to x


    For the first less than 34 from left
    =INDEX(A1:L1,MATCH(TRUE,A1:L1<=34,0))
    For the first large which is less than 34 from left
    =INDEX(A1:L1,MATCH(MAX(IF(A1:L1<34,A1:L1)),A1:L1,0))
    For address:
    =CELL("address",INDEX(A1:L1,MATCH(TRUE,A1:L1<=34,0)))
    and
    =CELL("address",INDEX(A1:L1,MATCH(MAX(IF(A1:L1<34,A1:L1)),A1:L1,0)))



    All arrays

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!