How to find the THIRD last numeric value in a row

[DT022]

Hi everyone,

I have a strip of alphabetical and numerical values across a row contained in cells B6 to J6 as follows:

56 dnp 24 dnp dnp 47 dnp 33 dnp

Thanks to this site, I have previously used the following formula to find the LAST numeric value in this range:

=INDEX(B6:J6,MATCH(9E+300,B6:J6))

I have also used the following formula to find the SECOND LAST numeric value in this range:

=LOOKUP(9E+300,B6:INDEX(B6:J6,MATCH(9E+300,B6:J6)-1))

I now need to find the THIRD LAST numeric value in this range. I tried to use the formula:

=LOOKUP(9E+300,B6:INDEX(B6:J6,MATCH(9E+300,B6:J6)-2))

but this did not give me the right answer. I have a number of rows of data just like the above strip (56 dnp 24 dnp etc etc) but with different combinations of numbers and "dnp" entries, and the formula above works on most of these strips, however when there is a certain combination of numbers and "dnp" entries across the columns (such as the one above), the formula for the THIRD LAST numeric value does not work correctly.

Does anyone know whether there is a formula to determine the THIRD LAST numeric value in a row, regardless of where the "dnp" entries are located within the row of data?

Thanks in advance...

[Andrew-R]

How about this:

=INDEX(6:6,LARGE(INDEX(COLUMN(B6:J6)*ISNUMBER(B6:J6),0),3))

The number in bold indicates which column you're after; 1 gives you the last column containing a number, 2 the 2nd last, 3 the 3rd last and so on.

[Bob Phillips]

If you had a 0 in there rather than nothing it should work fine.

[Cutter]

This works for the sample you've given (numeric every other column) but otherwise not.

=INDEX(B6:ZZ6,,COUNT(B6:K6)*2-5)

[daddylonglegs]

Ths "array formula" will give you the nth number from the end

=INDEX(B6:J6,LARGE(IF(ISNUMBER(B6:J6),COLUMN(B6:J6)-COLUMN(B6)+1),3))

[Fotis1991]

@ Bob.

If you had a 0 in there rather than nothing it should work fine.

Where, Bob?

[DT022]

Thanks for your response Andrew.

This formula works well, however there is one wrinkle in it in the scenario where I do not have 3 numeric entries within the range, for example:

dnp dnp 13 dnp dnp 34 dnp dnp dnp

In this case, when I run the formula it returns a number (not a #N/A) and also creates a circular reference within my spreadsheet.

Is there any way you can think of fixing this up?

Thanks again

[Cutter]

@Fotis
I believe Bob meant the zero is for the 3rd argument for the MATCH()

[DT022]

Thanks for your response Andrew.

This formula works well, however there is one wrinkle in it in the scenario where I do not have 3 numeric entries within the range, for example:

dnp dnp 13 dnp dnp 34 dnp dnp dnp

In this case, when I run the formula it returns a number (not a #N/A) and also creates a circular reference within my spreadsheet.

Is there any way you can think of fixing this up?

Thanks again

It looks like I can fix the circular reference by making this formula an array formula as suggested by daddylonglegs. This will give me a "0" number in the target cell if there are less than 3 numeric cells within my range. I can then remove these zeroes (which is what i need to do) by putting an IF function around the array formula to insert my desired ("NA") reference if the target cell is 0.

A little bit clunky but seems to achieve the desired result.

Thanks all

[Fotis1991]

Thanks Cutter.

I'll take a look there.

[daddylonglegs]

Try using my suggested formula within IFERROR, i.e.

=IFERROR(INDEX(B6:J6,LARGE(IF(ISNUMBER(B6:J6),COLUMN(B6:J6)-COLUMN(B6)+1),3)),"NA")

[DT022]

Try using my suggested formula within IFERROR, i.e.

=IFERROR(INDEX(B6:J6,LARGE(IF(ISNUMBER(B6:J6),COLUMN(B6:J6)-COLUMN(B6)+1),3)),"NA")

Thanks daddylonglegs, this works perfectly!