Searching for #N/A

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bgetson@gmail.com - 27 Feb 2007 05:26 GMT

I'm trying to create a formula that will tell me where the last
occurance of #N/A occurs in a table. If I have,

1 2 3 4 5
#N/A #N/A #N/A 2% 8%

I want a formula to tell me that the last occurance of #N/A is in
column 3, or that the first occurance of a real number is in column 4.
You can assume that the sets of #N/A will be contiguous.

Thank you.
-bgetson

Reply to this Message

Max - 27 Feb 2007 06:09 GMT

One way ..

Assuming data in row2, in A2 across,
Array-entered (press CTRL+SHIFT+ENTER) in say B1:
=MATCH(TRUE,ISNUMBER(2:2),0)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
On Feb 27, 1:26 pm, bget...@gmail.com wrote:

> I'm trying to create a formula that will tell me where the last
> occurance of #N/A occurs in a table. If I have,
[quoted text clipped - 8 lines]
> Thank you.
> -bgetson

Reply to this Message

Max - 27 Feb 2007 06:13 GMT

Some clarifications ..

The earlier array-entered:
=MATCH(TRUE,ISNUMBER(2:2),0)
will return the col number of the 1st occurrence of a real number

Adjusting arithmetically for it, array-entered:
=MATCH(TRUE,ISNUMBER(2:2),0)-1
will hence return the last occurrence of #N/A here, in your instance
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

bgetson@gmail.com - 28 Feb 2007 07:07 GMT

This is great. All of these solve my problem. I hadn't realized that
there was an ISNUMBER function that would have helped.

Thank you.
-bgetson

Reply to this Message

Max - 28 Feb 2007 13:23 GMT

Welcome. Good to hear that.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> This is great. All of these solve my problem. I hadn't realized that
> there was an ISNUMBER function that would have helped.
>
> Thank you.
> -bgetson

Reply to this Message

T. Valko - 27 Feb 2007 06:10 GMT

To find the relative position of the last instance of #N/A:

=LOOKUP(2,1/ISNA(A2:E2),COLUMN(A2:E2)-MIN(COLUMN(A2:E2))+1)

To find the relative position of the first number:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=MATCH(1,--ISNUMBER(A2:E2),0)

Biff

> I'm trying to create a formula that will tell me where the last
> occurance of #N/A occurs in a table. If I have,
[quoted text clipped - 8 lines]
> Thank you.
> -bgetson

Reply to this Message