1-6 staging based on three cells

[sublim1grl]

Hello,

I have been racking my brain and the internet, but can't figure out how to have multiple responses based on multiple criteria. It has to have more than a True, False response, because there are 6 stages. The VC is what really determines the final 5th and 6th stage. For simplicity:

Stage 1: Hp=>3, Ctx=0, VC=0
Stage 2: Hp=>6, Ctx=0, VC=0
Stage 3: Hp=>9, Ctx=>1, VC=0
Stage 4: Hp=>15, Ctx=>4, VC=0
Stage 5: Hp=>100, Ctx=>20, VC=0
Stage 6: Hp=>100, Ctx=>100, VC=<1

<TABLE style="WIDTH: 156pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=207 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 31pt; mso-width-source: userset; mso-width-alt: 1499" span=2 width=41><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Hp</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 46pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=61>Ctx</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 31pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=41>VC</TD><TD class=xl31 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 31pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=41>Stage</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl33 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>9.5</TD><TD class=xl34 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>12</TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>0</TD><TD class=xl35 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>8.5</TD><TD class=xl38 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num>11</TD><TD class=xl36 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num>0</TD><TD class=xl35 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl40 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>27</TD><TD class=xl41 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="12.333333333333334">12</TD><TD class=xl39 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num>3</TD><TD class=xl35 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl40 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8 0.5pt; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>11</TD><TD class=xl41 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num>0</TD><TD class=xl39 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num>0</TD><TD class=xl35 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>2</TD></TR></TBODY></TABLE>

I was able to create a simple one for stages 5&6 using, but don't know how to expand it.

=IF(AND(L5 >= 5, H5 > 5, N5>1),"6", "5" )

Advice??

 

[Gerald Higgins]

Hi, welcome to the board.

There are probably several ways to do this.
Can I just check, how do the cell references L5, H5 etc, relate to Hp, Ctx, VC etc ?

 

[sulakvea]

hi,

as G. Higgins suggested, there are a few ways to do it. here's one, with nested IF's. i would go with it since the conditions are overlapping (i.e. Stage 6 technically would include stage 5, etc)

=if(and(Hp=>100, Ctx=>100, VC=<1), 6, if(and(Hp=>100, Ctx=>20, VC=0), 5, if(and(Hp=>15, Ctx=>4, VC=0), 4, if(and(Hp=>9, Ctx=>1, VC=0), 3, if(and(Hp=>6, Ctx=0, VC=0), 2, if(and(Hp=>3, Ctx=0, VC=0), 1, ""))))))

replace HP, Ctx, VC with corresponding cells.

 

[sublim1grl]

This was very helpful and I appreciate the prompt respons, excuse the slow reply! I keep learning as I go and this forum is a great resource Thanks!!!!