Assistance with multi-cell formula

[WashingtonDC]

I need to generate a calculated cell (high risk vs. low risk) based on the following criteria of 4 other cells:

1. Number of hospital readmission within 30 days of discharge in the past year : 1 readmission = 2 points
2. Number of ED visits in the last 6 months : 1 ED visit = 2 points
3. Number of hospitalizations in the last 6 months : 1 hospitalization = 3 points

The score needs to be added up and if it is greater then or equal to 7 risk = "high". This is all superseded by " Hospitalization in the last 30 days". If this cell equals "yes", regardless of score of the other 3 cells, then risk is "high"

 

[DRSteele]

I think this is what you're after. If you don't want the reference table, you can hard-code the values into the formula. Copy F8 downwards.

	A	B	C	D	E	F
Able	Yes
Baker	No
Charlie	No
Dog	Yes
Easy	No
Fox	No

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F8CBAD"]Condition[/TD]
[TD="bgcolor: #F8CBAD"]Points[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE4D6"]Re-admission_30days[/TD]
[TD="bgcolor: #FCE4D6, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE4D6"]ED visits_6months[/TD]
[TD="bgcolor: #FCE4D6, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE4D6"]Hospiltalisations_6months[/TD]
[TD="bgcolor: #FCE4D6, align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE4D6"]Risk Hurdle[/TD]
[TD="bgcolor: #FCE4D6, align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFF2CC"]Patient[/TD]
[TD="bgcolor: #FFF2CC"]Re-admission_30days[/TD]
[TD="bgcolor: #FFF2CC"]ED visits_6months[/TD]
[TD="bgcolor: #FFF2CC"]Hospiltalisations_6months[/TD]
[TD="bgcolor: #FFF2CC"]Hospiltalisations_30days[/TD]
[TD="bgcolor: #FFF2CC"]RISK[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #E2EFDA"]High[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #E2EFDA"]High[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #E2EFDA"]Low[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #E2EFDA"]High[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #E2EFDA"]High[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #E2EFDA"]Low[/TD]

</tbody>

Sheet56​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F8[/TH]
[TD="align: left"]=IF(OR(E8="Yes",(B8*$C$2+C8*$C$3+D8*$C$4)>=$C$5),"High","Low")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]