Countif with decimals to give a percentage..HELP!!

**mrhogan** · 11-19-2008, 12:20 PM

Hi, This one sounds easy but I have spent a lot of time on it and no success. I have a list of students with various levels next to each name. EG: 4.5, 5.6, 5.7, 6.1 etc. From this range I want to work out the percentages of achiveing Level 5, 5 and above, Level 6, 6 and above. By doing a countif it counts 5 not 5.1 5.2 etc. How do I get over this? Attached is a sample. Thanks
M

**NBVC** · 11-19-2008, 12:25 PM

What Countif() formulas are you using?

=Countif(A2:A3,">=5") should count all values greater than or equal to 5.

**mrhogan** · 11-19-2008, 12:31 PM

Hi, Thanks

Yes that works for all level 5 or above but how do I then add an AND so its >=5 AND <6??

M

**NBVC** · 11-19-2008, 12:32 PM

=Countif(A2:A3,">=5")-Countif(A2:A3,">=6")

**oldchippy** · 11-19-2008, 12:33 PM

Hi,

Is this what you are trying to do?

**sweep** · 11-19-2008, 12:36 PM

Hi,

I think that the following will give you the required values.

Level 5 =COUNTIF(A2:A23,"=5")/COUNT(A2:A23)*100
Level 5+ =(COUNTIF(A2:A23,">5")-COUNTIF(A2:A23,"=6")-COUNTIF(A2:A23,">6"))/COUNT(A2:A23)*100
Level 6 =(COUNTIF(A2:A23,"=6")/COUNT(A2:A23))*100
Level 6+ =(COUNTIF(A2:A23,">6")/COUNT(A2:A23))*100

**mrhogan** · 11-19-2008, 12:50 PM

Originally Posted by oldchippy

Hi,

Is this what you are trying to do?

From the attached sample. The number of students achieving level 4 is 1, Level 5 is 15, level 6 is 6, LEvel 7 is 0. So the percentages that I worked out by hand are: Level 5 is 5%, Level 5+ is 95%, Level 6 is 6% and Level 6+ is 6% (as there are no Level 7 in the sample, however there could be)

Thanks again!

M

**mrhogan** · 11-19-2008, 12:53 PM

Originally Posted by NBVC

=Countif(A2:A3,">=5")-Countif(A2:A3,">=6")

By doing that you will minus the result of all students level 5 or above minus the students who are level 6 and above.

Thanks I am sure we are nearly there!

m

**NBVC** · 11-19-2008, 12:55 PM

Originally Posted by mrhogan

By doing that you will minus the result of all students level 5 or above minus the students who are level 6 and above.

Thanks I am sure we are nearly there!

m

Which will leave students between..

**mrhogan** · 11-19-2008, 12:56 PM

Originally Posted by sweep

Hi,

I think that the following will give you the required values.

Level 5 =COUNTIF(A2:A23,"=5")/COUNT(A2:A23)*100
Level 5+ =(COUNTIF(A2:A23,">5")-COUNTIF(A2:A23,"=6")-COUNTIF(A2:A23,">6"))/COUNT(A2:A23)*100
Level 6 =(COUNTIF(A2:A23,"=6")/COUNT(A2:A23))*100
Level 6+ =(COUNTIF(A2:A23,">6")/COUNT(A2:A23))*100

For your level 5 it returns only those cells with 5 not 5.1,5.2 etc.
Thanks

M

**mrhogan** · 11-20-2008, 04:24 AM

Thanks all. This gives me my solution:

Level 5: =COUNTIF($A$2:$A$23,">=5")-COUNTIF($A$2:A$23,">=6")
Level 5+: =COUNTIF($A$2:$A$23,">=5")
Level 6: =COUNTIF($A$2:$A$23,">=6")-COUNTIF($A$2:A$23,">=7")
Level 6+: =COUNTIF($A$2:$A$23,">=6")

The only thing is when I added /count(A2:A23) it did not work, I think its working out something before doing something else??? Anyway got all the numbers working, now I just need to apply it to the 150 live student levels!

M