Thread: text to formula help needed: risk colors

1. text to formula help needed: risk colors

Hi everyone,

I have a risk tracker basic risk colors (red, green, amber, grey) for 4 risk categories (scope, budget, resources, schedule) and I want to turn them into a number to help me generate an overall risk level for each project.
I would like to give the following values: green=1, amber=10, red=100, grey=0, so a project with 1 amber and 3 green statuses would have a Risk Score of 103 (examples below).

Could someone please help me with a formula to calculate the Risk Score automatically based on the Red/Amber/Green/Grey values in the risk columns?

 project name Scope R. Budget R. Resource R. Schedule R. Risk Score Project 1 Red Green Green Amber 112 Project 2 Amber Green Green Green 13

Thanks in advance !

2. Try this

Code:
`=SUMPRODUCT(COUNTIF(B2:E2,{"Green","Amber","Red","Grey"})*{1,10,100,1})`

3. Originally Posted by Bob Phillips
Try this

Code:
`=SUMPRODUCT(COUNTIF(B2:E2,{"Green","Amber","Red","Grey"})*{1,10,100,1})`
Thanks a lot Bob !

4. Originally Posted by Bob Phillips
Try this

Code:
`=SUMPRODUCT(COUNTIF(B2:E2,{"Green","Amber","Red","Grey"})*{1,10,100,1})`
apologies for the extra spam. I know I am doing something wrong but I can't figure out what.
Hopefully the attachment with the error screenshot works.

5. What SPAM???

What is your PC's locale? I suggest you try this:

=SUMPRODUCT(COUNTIF(B2:E2;{"Green";"Amber";"Red";"Grey"})*{1;10;100;1})

6. Originally Posted by AliGW
What SPAM???

What is your PC's locale? I suggest you try this:

=SUMPRODUCT(COUNTIF(B2:E2;{"Green";"Amber";"Red";"Grey"})*{1;10;100;1})
Thanks a lot. That did it
Swiss PC setup.

7. = European locale = semi-colons instead of commas (because of the potential confusion with a decimal separator)

Glad to have helped.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•