Calculating risk

[Grazzyj]

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.

 

[admiral100]

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]

 

[Grazzyj]

Thanks admiral100! That is absolutely spot on. Cheers