Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 
share|improve this question

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")
share|improve this answer

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).

share|improve this answer
    
Thank you all for your help! The attached link is also very helpful! – user56517 Nov 22 '10 at 20:31

You can use ISNA function of excel as well.

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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .