I would like to know what the format would beif I want to format and calculate percentages. I.e. one square is 20%, I have 5 diff colours and can have any combination of colours to make up 100%

Hi Anni

Thank you for your question; and welcome to the forum.

You can create conditional formats for a group of cells that contain percentages by going to the Home Ribbon and selecting the Conditional Formatting button from within the Styles group. If you go to the Highlight Cell Rules option and select the Value between option, this will allow you to set different colours for different ranges, e.g. 0-20% is red; 21-40% is orange - so on and so forth. You will need to set these rules one at a time.

I hope this helps.
Amanda

Thanks for that Amanda, that's very useful, however not quite what I'm after - I think I didn't express myself correctly enough :-)

What I work on is a weekly planner, so five squares = 5 days of the week (20% per day).

Someone might work 3 days on a billable project, so 3 squares are coloured green (horizontally) and then have 2 days where they are available, say red.

I need to do weekly forecasts of how many days people are working or free or on training etc.

Can that be done - that I count coloured squares basically

(the answer for the above example would then say 60% green and 40% red)

Thanks!!

Hi Anni

Thanks for clarifying.

I think you could only count based on the formatting of a cell if you did some VBA coding.

You can however count the number of times a word appears within a range, so if you had unavailable and available (or even U and A for short), you can use COUNTIF to count how many times each appears; then you could divide this by the total number of days to get a percentage.

Then you could apply conditional formatting to colour in addition if you wanted (e.g. if cell contains U, formatting is green; if cell contains A, formatting is red).

I have attached an example for you of how this could work. I hope this helps.

Amanda

Attached files...

Anni Excel example.xls