Calculating risk

Grazzyj

New Member
Joined
Feb 10, 2017
Messages
15
Hi All,

I need a formula to look at values in 3 columns and then based on what is displayed assign a score. The first column is a status (green, yellow, red), the 2nd is a yes or no column and so is the 3rd. So for example if the first column is red and the other 2 are "yes" then it needs to assign a score of 5 for risk level. Then say if the first column is yellow and the second is "yes" but the 3rd is "no" then assign a different score.

Is this possible?

Thanks in advance guys.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi,

Maybe this:-

The key to calculating : [TABLE="width: 350"]
<tbody>[TR]
[TD="width: 70, align: left"]no=0[/TD]
[TD="width: 70, align: left"]yes=1[/TD]
[TD="width: 70, align: left"]green=1[/TD]
[TD="width: 70, align: left"]yellow=2[/TD]
[TD="width: 70, align: left"]red=3[/TD]
[/TR]
</tbody>[/TABLE]


D1 =SUMPRODUCT(COUNTIF(A1,{"green","yellow","red"}),{1,2,3})+COUNTIF(B1:C1,"yes")

[TABLE="width: 350"]
<colgroup><col width="70" span="5" style="width:52pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"]A[/TD]
[TD="class: xl63, width: 70"]B[/TD]
[TD="class: xl63, width: 70"]C[/TD]
[TD="class: xl63, width: 70"]D[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]green[/TD]
[TD="class: xl63"]no[/TD]
[TD="class: xl63"]no[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]green[/TD]
[TD="class: xl63"]no[/TD]
[TD="class: xl63"]yes[/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]yellow[/TD]
[TD="class: xl63"]yes[/TD]
[TD="class: xl63"]yes[/TD]
[TD="class: xl63"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]red[/TD]
[TD="class: xl63"]yes[/TD]
[TD="class: xl63"]yes[/TD]
[TD="class: xl63"]5[/TD]
[/TR]
[TR]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]red[/TD]
[TD="class: xl63"]no[/TD]
[TD="class: xl63"]no[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"]red[/TD]
[TD="class: xl63"]yes[/TD]
[TD="class: xl63"]no[/TD]
[TD="class: xl63"]4[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,225,229
Messages
6,183,729
Members
453,185
Latest member
radiantclassy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top