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 :

no=0

yes=1

green=1

yellow=2

red=3

<tbody>
</tbody>

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

	A	B	C	D
1	green	no	no	1
2	green	no	yes	2
3	yellow	yes	yes	4
4	red	yes	yes	5
5	red	no	no	3
6	red	yes	no	4

<colgroup><col width="70" span="5" style="width:52pt"> </colgroup><tbody>
</tbody>

 

[Grazzyj]

Thanks admiral100! That is absolutely spot on. Cheers