Forum Discussion
Calculate Percentage of cell range of cells with value
Hello,
I wish to calculate the percentage of a range of cells (column), but only if they have a value other than '0' (some lazy conditional formatting on my part). It pertains to calculating students were present (which I indicate with a 'P').
However, I obviously want to calculate the percentage based upon the lessons they had, not the whole range (future lessons).
So, there will be 100 lessons (cell range) total, but if the student had 10 lessons where he/she was present 9 times, the percentage should be 90%.
Thank you for reading this post, and thank you in advance for your consideration.
Martin
6 Replies
So far I came up with this formula. Note that the range is a lot bigger, but the idea is exactly the same.
=COUNTA(($D$12:$D$211)-COUNTIF($D$12:$D$211,"=0"))/COUNTA($D$12:$D$211,"P")
However, the result stays 0.50% regardless of how many P's and/ or A's I have filled out.
I think I'm using a wrong operator somewhere....
- Can you share some sample data please?
Hi Jan Karel,
Thank you for being willing to look at my problem. :)
I hope this example makes sense. So if 10 classes happened the percentage of being present should be calculated based on 10 classes. If 15 classes happened the percentage should be based on that. etc.
Here P stands for Present, and A for Absent.
In my spreadsheet I will use 'A', 'P' and 'WN' (Without Notice). However, as all the cells that 'appear' blank will actually have a value of '0' because the original cells have reference formulas, anything as not '0' could be counted as a 'lesson'.
Does that make sense?
Kind regards,
Martin
Do you mean that one "p" is equal to 10%? if there are 9ps then the percentage should be 90%? if yes then we can solve this problem with the help of countif function.
