Logic Functions on Excel 2007

Question

I am calculating the cycle time for a process. For example: On my Weekends-Holiday column, #N/A means working days and Dayoff means weekends/holidays. My cycle time will need to exclude weekends and holidays. How can I setup a function to display #N/A = 1 (day) and Dayoff = 0 (day) at the Cycle Time column so I can sum up the days? Thanks you in advance for you help.

Date      Weekends-holidays Cycle Time in days 
12/18/09 #N/A  
12/19/09 Dayoff  
12/20/09 Dayoff  
12/21/09 #N/A  
12/22/09 #N/A  
12/23/09 #N/A  
12/24/09 #N/A  
12/25/09 Dayoff  
12/26/09 Dayoff  
12/27/09 Dayoff  
12/28/09 #N/A 

Answer 1

I am assuming that the #N/A are actually formula errors and that your example table starts in cell A1

place this into cell C2 (under the "Cycle Time in days" column) and fill it down

=IF(ISERROR(B2),1,0)

Alternatively if you want to shortcut to the COUNT of #N/A you may be able to use the COUNTIF function with something like this.

=COUNTIF(B:B,"#N/A")

Answer 2

Doesn't a simple =if($B2="#N/A";1;(if($B2="Dayoff";0;"unexpected input in Weekends-Holidays column!") work? Or do you want something more complex?

If you don't know where to find basic information about Excel functions, try Microsoft's reference, it is pretty good. http://office.microsoft.com/en-us/excel-help/CH010064513.aspx?CTT=97

Also, for this kind of information, you may find it easier to use a dedicated Gantt tool instead. I wouldn't recommend Microsoft Project, because it is quite quirky, but you can try it if you happen to have it lying around as a part of a full office license. Else, there are free tools and some of them can import data from Excel (directly or through a CSV export).

Answer 3

You can use ISNA function of excel as well.

http://techonthenet.com/excel/formulas/isna.php