Formula for last numberic value in column
 

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

Formula for last numberic value in column
 

Try

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

--
HTH

Bob Phillips

"Phil Hageman" wrote in message
...
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