Assign value to Excel cells based on their colors

Question

I am working on a Project Management template in which I would like to have a visual overview of the status of different activities as follows:

STATUSES:

• Blue = Completed
• Green = In Progress
• Yellow = Delayed
• Red = Issue
• White/No color = Not Started

In each cell there will be the name of the responsible and the cell will be colored based on the status.

There are 4 columns of which I would like to calculate for each row the average (let's assume Blue is 100%, Green 50%. Yellow 30%, Red 20% and White 0%).

Can you please help me?

Thanks!

I tried with this link but it did not work

https://www.exceldemy.com/excel-formula-based-on-cell-color/#2_Excel_COUNTIF_and_SUMIF_Formula_by_Cell_Color

Answer 1

If you have the calculation (percentages) in D column, then you can use this number to generate the conditional formatting and also the label in column E. Since you don't have too many options, it would be enough to use SWITCH function. For example:

=SWITCH(D1:D5, 1, "Completed", 0.5, "In Progress", 0.3, "Delayed", 0.2, "issue", 
 0, "Not Started")

Here is the output: