Find last digit in row < X

  • If I have the following sequence:


    [TABLE="width: 1690"]

    [tr]


    [TD="class: xl64, width: 65, align: right"]35.9[/TD]
    [TD="class: xl64, width: 65, align: right"]35.4[/TD]
    [TD="class: xl64, width: 65, align: right"]34.9[/TD]
    [TD="class: xl64, width: 65, align: right"]34.1[/TD]
    [TD="class: xl64, width: 65, align: right"]33.9[/TD]
    [TD="class: xl64, width: 65, align: right"]33.7[/TD]
    [TD="class: xl64, width: 65, align: right"]33.5[/TD]
    [TD="class: xl64, width: 65, align: right"]33.5[/TD]
    [TD="class: xl64, width: 65, align: right"]33.5[/TD]
    [TD="class: xl64, width: 65, align: right"]33.5[/TD]
    [TD="class: xl64, width: 65, align: right"]33.7[/TD]
    [TD="class: xl64, width: 65, align: right"]33.7[/TD]
    [TD="class: xl64, width: 65, align: right"]33.7[/TD]
    [TD="class: xl64, width: 65, align: right"]34.3[/TD]
    [TD="class: xl64, width: 65, align: right"]35.1[/TD]
    [TD="class: xl64, width: 65, align: right"]36.1[/TD]
    [TD="class: xl64, width: 65, align: right"]36.6[/TD]
    [TD="class: xl64, width: 65, align: right"]36.8[/TD]
    [TD="class: xl64, width: 65, align: right"]37.0[/TD]
    [TD="class: xl64, width: 65, align: right"][/TD]
    [TD="class: xl64, width: 65, align: right"][/TD]
    [TD="class: xl64, width: 65, align: right"][/TD]
    [TD="class: xl64, width: 65, align: right"][/TD]
    [TD="class: xl64, width: 65, align: right"][/TD]
    [TD="class: xl64, width: 65, align: right"][/TD]
    [TD="class: xl64, width: 65, align: right"][/TD]

    [/tr]


    [/TABLE]


    I would like to find the last number in the row < 34 (i.e. the 33.7 in the 13th position)


    Thanks to this forum, I already have a formula for finding the last minimum value in the row: =LOOKUP(1,1/(MIN(B2:AR2)=B2:AR2),ADDRESS(ROW(B2),COLUMN(B2:AR2),4))


    As well as one for finding the FIRST number in the row < 34: =CELL("address",INDEX(B2:AR2,MATCH(TRUE,B2:AR2<=34,0)))


    But I cannot seem to find a formula that will give me the last number in the row that is less than 34.


    Thanks.

  • Re: Find last digit in row &lt; X


    Thanks, that works well.


    The only thing is that there are some blank cells at the end of the row and the formula doesn't exclude those. Is there anyway to overcome that other than altering the range? I have to apply it to about 3000 rows that are all of varying length

Participate now!

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