Ignore #NA while extracting date

[Chintan Trivedi]

I have the data table as follow:


A B C D


1 Dates 31-Jan-2013, 30-Jan-2013, 29-Jan-2013,


2 A 200, 220.5, 0,


3 B #NA, 0, 20,


4 C 354, 365, #NA,


I am using the formula:


=Max(index((B2:D2>0)*$B$1:#D1$1,0)) for A and like that for others


I want to extract the date for the first available data which is more than "0".


I am not getting the date for "B" and "C" as there is "#NA"


How I can ignore the "#NA" Without using array formula as my formula is also non-array


I have shared the document http://speedy.sh/VmPy4/Chintan-Trivedi.xlsx

 

[sdsurzh]

Hi C T,


Use Find and Replace to ignore #N/A.


Thanks,

Suresh Kumar S

 

[Chintan Trivedi]

I could not replace #NA with 0.


Any other way....

 

[NARAYANK991]

Hi Trivedi ,


Your #NA is not really #N/A , which is an Excel error value ; the way you have entered it , it is just a text string.


If it is really the Excel error value , then you can use the following non-array formula :


=INDEX($B$1:$D$1,MATCH(1,INDEX(($B2:$D2<>0)+0,1,0),0))


which will return the date corresponding to the first non-zero value.


Copied from : http://www.mrexcel.com/forum/excel-questions/30452-finding-first-instance-non-zero.html


Narayan