Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I convert numberic value to text in english? | Excel Worksheet Functions | |||
delete non numberic characters | Excel Discussion (Misc queries) | |||
Can you convert Excel column indicators from Alpha to Numberic? | Excel Discussion (Misc queries) | |||
Can you convert Excel column indicators from Alpha to Numberic? | Excel Discussion (Misc queries) | |||
alpha numberic cell | Excel Worksheet Functions |