Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

3 Answers 3

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.