Problems with adapting a formula

[mattbird]

Hi,

I'm currently producing an attendance list and recording a rolling percentage. I am using the following formula:

=IF(COUNTIFS($D11:$O11,"")=12,0,SUM(COUNTIFS($D11:$O11,{1,"D"}))/$U11) - inputted in cell V11 etc of table.

The formula work, but I would like to adapt it.

My table has the following key:

1 = attended ( this is counted in cell P11)
0 = did not attend (this is counted in cell S11)
A = Apologies (this is counted in cell R11)
D = Deputy (this is counted in cell Q11)

In cell U11 I have a sum to add P11:S11, which give me a total for the formula above.

I have added a new cell T11 which counts 'N' (not required) when placed in any cell D11:O11 and I don't want 'N' to affect the percentage. If all the cells D11:O11 are empty and I place an 'N' in any of them cells my percentage cell V11 returns #DIV/0! when I need it to show 0%.

example:

	Team	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Count '1'	Count 'D'	Count 'A'	Count '0'	Count 'N'	Total (P:S)	Attendance %
		D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
11	Team A	1	1											2	0	0	0	0	2	100%
12	Team A	1	1	1	1	1								5	0	0	0	0	5	100%
13	Team A	1	0											1	0	0	1	0	2	50%
14	Team B	1	0	1										2	0	0	1	0	3	67%
15	Team B	1	D											1	1	0	0	0	2	100%
16	Team C	1	A											1	0	1	0	0	2	50%
17	Team C	1	1	0	A	1	D	0						3	1	1	2	0	7	57%
18	Team D	1	N											1	0	0	0	1	1	100%
19	Team E	N	1											1	0	0	0	1	1	100%
20	Team F	N	1	1	N	1	0	N	D	N	N	1	A	4	1	1	1	5	6	71%
21	Team F	N																		#DIV/0! (needs to show 0%)
22	Team F	N		N		N		N												#DIV/0! (needs to show 0%)

How do I change the formula to not see N, if you understand what I mean.

I hope this make sense, as it hard to explain.

 

[Fluff]

Duplicate to: Need help adapting a formula

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.