Working out a percentage of complete checks and representing it in a graph

Hi all,

I have been playing around with a few check lists that we hold within the maintenance team. One such is the checking of emergency exit lighting systems. Each month, each of these should be checked (represented in my spreadsheet as A5:A52), I have introduced another column that is a simple ‘check complete’ (represented in my spreadsheet as H5:H52). What I am hoping to do, is have a count which will display of percentage of the cells between H5:H52 which are blank. Using COUNTIF will add them up, but then my percentage would be 4800%… I just cant figure out how to work the percentage of checks carried out of the light systems each month and then work that into a bar chart for visual display.
The tables for each month are all held on seperate sheets, labelled Jan through to Dec.

So the generalised question would be… how do I work out the percentage of 48 cells (H5:H52) that are currently ‘blank’ and then display that in a graph that shows all 12 months and what percentage of the checks were carried out during that month?

Is it even possible?

Thanks in advance.

Welcome to the forum!

This will be much easier to resolve with a sample workbook, which you can attach here by using the Go Advanced button.

View attachment 2017 Emergency Lighting test.xlsx Okay, I have made a very basic start. The original document was very simple, but was not getting used and to gauge the percentage of checks done, our technicians were 'ball park' guessing and entering manually (as can be seen on 'Previous Template' sheet).

Any help much appreciated.

OK, so go to the July sheet and add a few Y into column H to simulate some checks having been done. Now try this on your data sheet:

=COUNTIF(July!H5:H52,"<>")/ROWS(H5:H52)

Is that what you are wanting?

That is exactly what I was wanting Ali, thank you. I have very basic excel skills and knew how to create the count, but could not manage to figure how to make it a percentage of the chosen cells and not the addition of completed cells.

Now just need to figure how to display all 12 months on a bar Graph. I should be able to add validations and formatting to display months when unsatisfactory percentage of checks have/haven't been carried out. :thumb:

Glad to have helped!