Working out percentage of cells that are a specific colour

[Excelmakesmyheadhurt]

We are beginning to bring in self rostering at work and I need to work out some formulas for the spreadsheet to work in the way I need it to.

Members of staff will be able to access the spreadsheet to choose what shifts they would like to work on each day, with us aiming to approve at least 50% of these shifts.

If I were to change the cell colour of each approved shift, what formula would I need to use to be able to see the percentage of requested shifts that have been approved?

 

[RoryA]

No built-in function can read the colour of a cell. You would need VBA code for that (or an old Excel4 macro function) and you should be aware that colouring a cell does not trigger a recalculation so it is not easy to make sure that the formula results are always up to date. I would always recommend using actual data rather than colours if at all possible - e.g. with additional helper rows and/or columns where you can put some sort of 'approved' marker.

 

[Joe4]

Welcome to the Board!

Have a look here for different ways to count cells by color: How to Count COLORED Cells in Excel [Step-by-Step Guide + VIDEO]

Once you get the number of cells meeting your color condition, simply divide by the total number of cells (can get with the COUNTA function) to get your percentage.