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


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.

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.

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.

share|improve this answer

You must log in to answer this question.

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