Formula for last numberic value in column

[Phil Hageman]

In column G the “Graphs and Projections†worksheet I have the following
formula series:

Cell Cell formula
Value
50 =IF('Multi-Trend Chart'!D202<>"",'Multi-Trend Chart'!D202,#N/A) $192,805
51 =IF('Multi-Trend Chart'!D203<>"",'Multi-Trend Chart'!D203,#N/A) $199,864
52 =IF('Multi-Trend Chart'!D204<>"",'Multi-Trend Chart'!D204,#N/A) $201,698
53 =IF('Multi-Trend Chart'!D204<>"",'Multi-Trend Chart'!D204,#N/A) #N/A
54 =IF('Multi-Trend Chart'!D204<>"",'Multi-Trend Chart'!D204,#N/A) #N/A
etc etc #N/A
etc etc #N/A
100 etc $201,698

The “#N/A†feature of the formula causes Excel to not plot null or blank
values as zeros. I am using conditional formatting to make the “N/Asâ€
invisible. Works okay.

What I need is a formula for cell G100 that shows the last numeric value in
column G, not the “#N/A.†What would that formula be?

Or, is there a more efficient way to approach this?

Thanks, Phil

 

[Bob Phillips]

Try

=INDEX(G:G,MATCH(9.99999999999999E+307,G:G))