Trying to divide a cell value by a countif value

[mtaylor]

Hi Team,

Weird one this. I have set my formula to this: =(countif(C3:AI3,"*")/(AJ3)*100) and it works.

However I want it the other way round: =(AJ3)/(countif(C3:AI3,"*")*100) and it comes as 0, whereas it should be 100

Tell me doing something stupid!?

Thanks

Matt

 

[Peter_SSs]

Hard to tell without knowing what is in C3:AI3 ans AJ3

 

[mtaylor]

KG1A

Name

%

22/03

23/03

24/03

25/03

26/03

29/03

30/03

31/03

01/04

02/04

05/04

06/04

07/04

08/04

09/04

12/04

13/04

14/04

15/04

16/04

19/04

20/04

21/04

22/04

23/04

22/03

23/03

24/03

26/04

27/04

28/04

29/04

30/04

Days Present Total

Student's name

0

L

P

P

P

P

5

This is for registers. L and P represent Late and Present.

The 5 at the end is counting how many L's and P's appear in the dates 22/03 to 30/04.

Hope this is more useful than my original post?

Matt

 

[mtaylor]

The cell with the '5' in uses the formula: =COUNTIF($C3:$AI3,"P")+COUNTIF($C3:$AI3,"L")

 

[Peter_SSs]

If you are expecting 100 as the result of the 2nd formula, then perhaps you meant this?

=AJ3/COUNTIF(C3:AI3,"*")*100

 

[mtaylor]

Amazing!!

Thank you very much!!

 

[sanrv1f]

BTW, why use COUNTIF with *? why not COUNTA?

 

[mtaylor]

BTW, why use COUNTIF with *? why not COUNTA?

Hmmmmmm...never used counta.. Will have a gander today.

Thanks for the suggestion

 

[Peter_SSs]

BTW, why use COUNTIF with *? why not COUNTA?

I initially wondered if it was deliberate as they behave differently if any numerical values are in the range. Looking at post # 3 though that appears unlikely.

COUNTA may be all that is required in AJ3 as well?