Removing #DIV/0 but could be more complicated...

[mtaylor]

This is used for registration in a school during this Coronavirus situation.

Essentially teachers input the codes and a percentage is then calculated. This data is then analysed on another sheet showing the students absent for less than 61% of the week.

My issue is that if a teacher doesn't input a code for a student for the whole week a #DIV/0 code is given which affects the outcome of the sheet showing all the names.

Hopefully these pics will explain better...

KG1C

Name

Total %

22/03

23/03

24/03

25/03

26/03

Week 1 %

29/03

30/03

31/03

01/04

02/04

Week 2 %

05/04

06/04

07/04

08/04

09/04

Week 3 %

12/04

13/04

14/04

15/04

16/04

Week 4 %

19/04

20/04

21/04

22/04

23/04

Week 5 %

26/04

27/04

28/04

29/04

30/04

Week 6 %

28/04

29/04

30/04

Days Present Total

Tom

100

p

p

p

p

p

100

p

P

P

P

P

100

P

P

P

P

P

100

P

P

P

P

P

100

P

P

P

P

P

100

P

P

P

P

P

100

30

****

63

P

P

p

UA

UA

60

UA

p

UA

P

P

60

P

P

UA

P

P

80

P

P

P

UA

P

80

L

UA

P

P

UA

60

P

P

UA

UA

UA

40

19

harry

96

l

p

p

p

l

100

p

l

P

P

P

100

P

P

P

P

P

100

P

P

P

P

P

100

AA

P

P

P

P

80

#DIV/0!

24

Bill

62

UA

UA

l

l

p

60

l

p

UA

UA

UA

40

AA

AA

P

P

P

60

P

P

P

P

P

100

P

P

P

UA

UA

60

O

0

16

Ben

100

p

P

p

p

p

100

p

P

P

P

P

100

P

P

P

P

P

100

P

P

P

P

P

100

P

P

P

P

P

100

P

P

P

P

P

100

30

Week 6. I put the 'O' in to see if that was the problem and it works...

Below is the formula for the adding of names from the class sheets to the sheet showing less than 61% attendance children.

Many thanks in advance. My apologies if this is something basic. ?

 

[GraH]

Hi, avoiding "#DIV/0!" can be done like = IFERROR(your formula, "").

 

[mtaylor]

Hi, avoiding "#DIV/0!" can be done like = IFERROR(your formula, "").

Like this:

=IFERROR((COUNTIF(AG3:AK3,"P")+COUNTIF(AG3:AK3,"L")) / COUNTIF(AG3:AK3,"*") *100),"")

I get an error msg adding it like this...

 

[MARK858]

Maybe the below (untested as I can't copy/paste your image) and a couple of brackets in there just for clarity...
=IFERROR((COUNTIF(AG3:AK3,"P")+COUNTIF(AG3:AK3,"L") )/(COUNTIF(AG3:AK3,"*") *100),"")

 

[Peter_SSs]

For division by zero, you can specifically check the denominator rather that relying on the generic IFERROR function (which could hide other errors that you want to know about).
If the codes in AG:AK are being entered manually by the teachers then try

=IF(COUNTA(AG3:AK3),(COUNTIF(AG3:AK3,"P")+COUNTIF(AG3:AK3,"L"))/COUNTA(AG3:AK3),"")

 

[mtaylor]

For division by zero, you can specifically check the denominator rather that relying on the generic IFERROR function (which could hide other errors that you want to know about).
If the codes in AG:AK are being entered manually by the teachers then try

=IF(COUNTA(AG3:AK3),(COUNTIF(AG3:AK3,"P")+COUNTIF(AG3:AK3,"L"))/COUNTA(AG3:AK3),"")

Thanks Peter,
I couldn't work out where the *100 would go in the formula, I kept getting ERROR messgaes occur.

I think I can work it through using:

=IFERROR(((COUNTIF(AG3:AK3,"P")+COUNTIF(AG3:AK3,"L")) / COUNTIF(AG3:AK3,"*") *100))

That seems to get the job done.

Thanks again

Matt

 

[mtaylor]

Maybe the below (untested as I can't copy/paste your image) and a couple of brackets in there just for clarity...
=IFERROR((COUNTIF(AG3:AK3,"P")+COUNTIF(AG3:AK3,"L") )/(COUNTIF(AG3:AK3,"*") *100),"")

Thanks Mark,
It turns out the formula you suggested messed with my division somewhat.

I had a play though and found that this works:

=IFERROR(((COUNTIF(AG3:AK3,"P")+COUNTIF(AG3:AK3,"L")) / COUNTIF(AG3:AK3,"*") *100))

Thanks again

Matt

 

[Peter_SSs]

I couldn't work out where the *100 would go in the formula,

Sorry, that was my mistake - I accidentally left it out. you shouldn't need an IFERROR. Try this one instead.

20 05 01.xlsm
	AG	AH	AI	AJ	AK	AL
3	P	P	P	P	P	100
4	p	P	UA	UA	UA	40
5
6					O	0
7	P	P	P	P	P	100
Avoid Div 0

Cell Formulas
Range	Formula
AL3:AL7	AL3	=IF(COUNTA(AG3:AK3),(COUNTIF(AG3:AK3,"P")+COUNTIF(AG3:AK3,"L"))/COUNTA(AG3:AK3)*100,"")

 

[mtaylor]

Sorry, that was my mistake - I accidentally left it out. you shouldn't need an IFERROR. Try this one instead.

20 05 01.xlsm
	AG	AH	AI	AJ	AK	AL
3	P	P	P	P	P	100
4	p	P	UA	UA	UA	40
5
6					O	0
7	P	P	P	P	P	100
Avoid Div 0

Cell Formulas
Range	Formula
AL3:AL7	AL3	=IF(COUNTA(AG3:AK3),(COUNTIF(AG3:AK3,"P")+COUNTIF(AG3:AK3,"L"))/COUNTA(AG3:AK3)*100,"")

Smashing - Cheers Peter

 

[Peter_SSs]

Smashing - Cheers Peter