#N/A Conversion

[Aaron]

Hi - how do I go about converting a value that shows #N/A to a number like 0?
I want to use the value to calculate a sum of a column but the #N/As in the
column prevent me from doing so. This is what the spreadsheet looks like:

A B
1
2 Date Count
3 11/1/2009 0
4 11/2/2009 #N/A
5 11/3/2009 0
: : :
: : :

Column A1 is the first day of the month. The formula for Column A2 is A1+1.
The formula for Column A3 is A2+1 and it keeps going until the end of the
month.

Column B1 is a formula that refers to another sheet and uses hlookup.
=IF(ERROR.TYPE(HLOOKUP(A3,Sheet1!$1:$138,127,FALSE))=ERROR.TYPE(#N/A),0,1)

On Sheet1 not all of the dates are there - the weekends are excluded from
the sheet. On Sheet2 I have all of the dates there. I'm trying to automate
Sheet2 so that if I enter the first day of the month in A3 that it'll pull
the info from Sheet1 into Sheet2 where I can calculate the sum of the
numbers. Sheet1 is more of an input area with lots of info whereas Sheet2 is
for presentation of limited information.

So basically the IF statement above is looking for a match on the date, if
it can't find the date, it should be a zero otherwise a 1 should be there.
It works if it can't find a date but if there is a date it shows an #N/A now.
I can't sum the column up if there is an #N/A. I thought that the IF
statement should work whether true or false. Can someone assist in pointing
to the right direction in fixing the statement? I'm almost at wits end with
this. Thank you! Aaron

 

[Max]

You could use an IF(ISNA(...),0, .. construct to return a zero for any
unmatched cases.

For your example, try in B1:
=IF(ISNA(HLOOKUP(A3,Sheet1!$1:$138,127,0)),0,HLOOKUP(A3,Sheet1!$1:$138,127,0))
if you want to return a zero for any unmatched cases, and the actual extract
from row 127 for any matched cases

Or maybe just this:
=IF(ISNA(HLOOKUP(A3,Sheet1!$1:$138,127,0)),0,1)
would suffice for your purposes? (using your hlookup construct)

Even this might suffice to give you the expected results:
=IF(COUNTIF(Sheet1!1:1,List!A3),1,0)

Btw ... a better newsgroup to post such questions would be:
excel.worksheet.functions

Above any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik