Results 1 to 7 of 7

Thread: text to formula help needed: risk colors

  1. #1
    Seeker gn28's Avatar
    Join Date
    Jun 2015
    Posts
    5
    Articles
    0
    Excel Version
    office 365

    Question text to formula help needed: risk colors



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,621
    Articles
    0
    Excel Version
    O365
    Try this

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

  3. #3
    Seeker gn28's Avatar
    Join Date
    Jun 2015
    Posts
    5
    Articles
    0
    Excel Version
    office 365
    Quote Originally Posted by Bob Phillips View Post
    Try this

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

  4. #4
    Seeker gn28's Avatar
    Join Date
    Jun 2015
    Posts
    5
    Articles
    0
    Excel Version
    office 365
    Quote Originally Posted by Bob Phillips View Post
    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.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Capture.JPG 
Views:	6 
Size:	58.6 KB 
ID:	9268  

  5. #5
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,235
    Articles
    0
    Excel Version
    Office 365 Subscription
    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})
    Ali
    Enthusiastic self-taught user of MS Excel!

  6. #6
    Seeker gn28's Avatar
    Join Date
    Jun 2015
    Posts
    5
    Articles
    0
    Excel Version
    office 365
    Quote Originally Posted by AliGW View Post
    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. #7
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,235
    Articles
    0
    Excel Version
    Office 365 Subscription
    = European locale = semi-colons instead of commas (because of the potential confusion with a decimal separator)

    Glad to have helped.
    Ali
    Enthusiastic self-taught user of MS Excel!

Posting Permissions

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