formula to calculate timecard compliance with multiple variables

[lovelyanai]

Hello,

I was hoping someone could help me figure out a formula to calculate the compliance percentage of timecard submissions

I have the following information in a spreadsheet set up as below. The codes that are non-compliant are M, R, I and the codes that are compliant are C, A, X, W. What formula could I use to figure out the complaince?

Please Login or Register  to view this content.

(so this particular employee would be 1/4 to get percentage if doing it by hand)

[JBeaucaire]

Here's one way:
Data Range

	C	D	E	F	G	H	I	J
2		1/8/2017	1/15/2017	1/22/2017	1/29/2017		Compliance	this is an array formula
3	John	R	I	M	I		0%	=SUM(--ISNUMBER(SEARCH($D3:$G3,"CAXW")))/COUNTA($D3:$G3)
4	Sam	M	C	A	I		50%

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.