FreeRangeJ
New Member
- Joined
- Feb 7, 2013
- Messages
- 40
I have a column of GROUP (1 through 5), another column of [C] TERRITORY (1 or 2) and a column of [E] GROUP SPACE AVAILABLE and depending on the 10 possible variables of those 2 columns I have 3 potential outcomes, [E]*0 , [E]*1 or [E]*2 to go incolumn F if this rule is at all possible in a single cell expression, i.e. without a lookup table using Index/Match, I'm picturing something like a nested IF AND OR statement but can't quite picture the flow / order
<tbody>
[TD="class: xl68"]Site ID[/TD]
[TD="class: xl69, width: 64"]Ranked Group[/TD]
[TD="class: xl69, width: 64"]Territory[/TD]
[TD="class: xl69, width: 64"]Stock ID[/TD]
[TD="class: xl69, width: 92"]Group Space Available (cm)[/TD]
[TD="class: xl70, width: 92"]Group Space Used (cm) Desired Result[/TD]
[TD="class: xl71"]3456[/TD]
[TD="class: xl72"]One[/TD]
[TD="class: xl72"]One[/TD]
[TD="class: xl72, align: right"]12345[/TD]
[TD="class: xl72, align: right"]50[/TD]
[TD="class: xl73"][=E2*2][/TD]
[TD="class: xl71"]7890[/TD]
[TD="class: xl72"]Two[/TD]
[TD="class: xl72"]One[/TD]
[TD="class: xl72, align: right"]23456[/TD]
[TD="class: xl72, align: right"]50[/TD]
[TD="class: xl73"][=E3*2][/TD]
[TD="class: xl71"]2345[/TD]
[TD="class: xl72"]Three[/TD]
[TD="class: xl72"]One[/TD]
[TD="class: xl72, align: right"]34567[/TD]
[TD="class: xl72, align: right"]50[/TD]
[TD="class: xl73"]{=E4*0][/TD]
[TD="class: xl71"]5678[/TD]
[TD="class: xl72"]Four[/TD]
[TD="class: xl72"]One[/TD]
[TD="class: xl72, align: right"]45678[/TD]
[TD="class: xl72, align: right"]50[/TD]
[TD="class: xl73"][=E5*1][/TD]
[TD="class: xl71"]9876[/TD]
[TD="class: xl72"]Five[/TD]
[TD="class: xl72"]One[/TD]
[TD="class: xl72, align: right"]56789[/TD]
[TD="class: xl72, align: right"]50[/TD]
[TD="class: xl73"][=E6*1][/TD]
[TD="class: xl71"]2345[/TD]
[TD="class: xl72"]One[/TD]
[TD="class: xl72"]Two[/TD]
[TD="class: xl72, align: right"]76543[/TD]
[TD="class: xl72, align: right"]100[/TD]
[TD="class: xl73"][=E7*2][/TD]
[TD="class: xl71"]6547[/TD]
[TD="class: xl72"]Two[/TD]
[TD="class: xl72"]Two[/TD]
[TD="class: xl72, align: right"]87654[/TD]
[TD="class: xl72, align: right"]100[/TD]
[TD="class: xl73"][=E8*1][/TD]
[TD="class: xl71"]8745[/TD]
[TD="class: xl72"]Three[/TD]
[TD="class: xl72"]Two[/TD]
[TD="class: xl72, align: right"]98765[/TD]
[TD="class: xl72, align: right"]100[/TD]
[TD="class: xl73"][=E9*1][/TD]
[TD="class: xl71"]3465[/TD]
[TD="class: xl72"]Four[/TD]
[TD="class: xl72"]Two[/TD]
[TD="class: xl72, align: right"]65432[/TD]
[TD="class: xl72, align: right"]100[/TD]
[TD="class: xl73"][=E10*1][/TD]
[TD="class: xl71"]3567[/TD]
[TD="class: xl72"]Five[/TD]
[TD="class: xl72"]Two[/TD]
[TD="class: xl72, align: right"]54321[/TD]
[TD="class: xl72, align: right"]100[/TD]
[TD="class: xl73"][=E11*1][/TD]
</tbody>
<tbody>
[TD="class: xl68"]Site ID[/TD]
[TD="class: xl69, width: 64"]Ranked Group[/TD]
[TD="class: xl69, width: 64"]Territory[/TD]
[TD="class: xl69, width: 64"]Stock ID[/TD]
[TD="class: xl69, width: 92"]Group Space Available (cm)[/TD]
[TD="class: xl70, width: 92"]Group Space Used (cm) Desired Result[/TD]
[TD="class: xl71"]3456[/TD]
[TD="class: xl72"]One[/TD]
[TD="class: xl72"]One[/TD]
[TD="class: xl72, align: right"]12345[/TD]
[TD="class: xl72, align: right"]50[/TD]
[TD="class: xl73"][=E2*2][/TD]
[TD="class: xl71"]7890[/TD]
[TD="class: xl72"]Two[/TD]
[TD="class: xl72"]One[/TD]
[TD="class: xl72, align: right"]23456[/TD]
[TD="class: xl72, align: right"]50[/TD]
[TD="class: xl73"][=E3*2][/TD]
[TD="class: xl71"]2345[/TD]
[TD="class: xl72"]Three[/TD]
[TD="class: xl72"]One[/TD]
[TD="class: xl72, align: right"]34567[/TD]
[TD="class: xl72, align: right"]50[/TD]
[TD="class: xl73"]{=E4*0][/TD]
[TD="class: xl71"]5678[/TD]
[TD="class: xl72"]Four[/TD]
[TD="class: xl72"]One[/TD]
[TD="class: xl72, align: right"]45678[/TD]
[TD="class: xl72, align: right"]50[/TD]
[TD="class: xl73"][=E5*1][/TD]
[TD="class: xl71"]9876[/TD]
[TD="class: xl72"]Five[/TD]
[TD="class: xl72"]One[/TD]
[TD="class: xl72, align: right"]56789[/TD]
[TD="class: xl72, align: right"]50[/TD]
[TD="class: xl73"][=E6*1][/TD]
[TD="class: xl71"]2345[/TD]
[TD="class: xl72"]One[/TD]
[TD="class: xl72"]Two[/TD]
[TD="class: xl72, align: right"]76543[/TD]
[TD="class: xl72, align: right"]100[/TD]
[TD="class: xl73"][=E7*2][/TD]
[TD="class: xl71"]6547[/TD]
[TD="class: xl72"]Two[/TD]
[TD="class: xl72"]Two[/TD]
[TD="class: xl72, align: right"]87654[/TD]
[TD="class: xl72, align: right"]100[/TD]
[TD="class: xl73"][=E8*1][/TD]
[TD="class: xl71"]8745[/TD]
[TD="class: xl72"]Three[/TD]
[TD="class: xl72"]Two[/TD]
[TD="class: xl72, align: right"]98765[/TD]
[TD="class: xl72, align: right"]100[/TD]
[TD="class: xl73"][=E9*1][/TD]
[TD="class: xl71"]3465[/TD]
[TD="class: xl72"]Four[/TD]
[TD="class: xl72"]Two[/TD]
[TD="class: xl72, align: right"]65432[/TD]
[TD="class: xl72, align: right"]100[/TD]
[TD="class: xl73"][=E10*1][/TD]
[TD="class: xl71"]3567[/TD]
[TD="class: xl72"]Five[/TD]
[TD="class: xl72"]Two[/TD]
[TD="class: xl72, align: right"]54321[/TD]
[TD="class: xl72, align: right"]100[/TD]
[TD="class: xl73"][=E11*1][/TD]
</tbody>