Can this rule be applied in a sigle cell function?

[FreeRangeJ]

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

Site ID	Ranked Group	Territory	Stock ID	Group Space Available (cm)	Group Space Used (cm) Desired Result
3456	One	One	12345	50	[=E2*2]
7890	Two	One	23456	50	[=E3*2]
2345	Three	One	34567	50	{=E4*0]
5678	Four	One	45678	50	[=E5*1]
9876	Five	One	56789	50	[=E6*1]
2345	One	Two	76543	100	[=E7*2]
6547	Two	Two	87654	100	[=E8*1]
8745	Three	Two	98765	100	[=E9*1]
3465	Four	Two	65432	100	[=E10*1]
3567	Five	Two	54321	100	[=E11*1]

<tbody>
</tbody>

 

[steve the fish]

I read it and then reread it and i dont see a rule here. What is it?

 

[FreeRangeJ]

Sorry, you're right, I've been bending my head round this all night for a job I technically finished yesterday and I guess i'm not making mush sense!

The rule is that if it is Territory 1 , then groups 1 & 2 get 2x the group allocation of space, groups 4 & 5 get a single allocation of the group space but group 3 gets none
if it is territory 2 then group 1 gets a double allocation of the group space and the other groups 2 through 5 get a single allocation

Or another way of looking at it, Group 1 gets a double allocation whether territory 1 or 2 and all other groups get a single allocation of the space, except in territoty 1 group 3 doesn't get any allocation and that gets added onto group 2's allocation

i really don't know if that makes it any clearer!

 

[steve the fish]

Are those worded numbers really words? You would probably find the formulas easier if they were really numbers. Anyhow this works with what you have provided:

=E2*IF(C2="One",VLOOKUP(B2,{"one",2;"two",2;"three",0;"four",1;"five",1},2,0),IF(C2="Two",VLOOKUP(B2,{"one",2;"two",1;"three",1;"four",1;"five",1},2,0),0))

 

[FreeRangeJ]

No the worded number are just anonymised examples of the 5 variants of the Group Labels and the 2 variants of the Territory label I have, just trying to simplify my request as I've been on this since yesterday and I think i'm struggling to communicate my requirements to be honest

Just going to test your solution now, it's going into a s/sheet with aprox 600k rows that this would be popultaing against

I'll keep you posted!

 

[AlanY]

try this

Book1
	A	B	C	D	E	F
1	Site ID	Ranked Group	Territory	Stock ID	Group Space Available (cm)	Group Space Used (cm) Desired Result
2	3456	1	1	12345	50	100
3	7890	2	1	23456	50	100
4	2345	3	1	34567	50	0
5	5678	4	1	45678	50	50
6	9876	5	1	56789	50	50
7	2345	1	2	76543	100	200
8	6547	2	2	87654	100	100
9	8745	3	2	98765	100	100
10	3465	4	2	65432	100	100
11	3567	5	2	54321	100	100
Sheet1

Cell Formulas
Range	Formula
F2	=CHOOSE(C2,CHOOSE(B2,2,2,0,1,1),CHOOSE(B2,2,1,1,1,1))*E2

 

[jtakw]

Hi,

Following your description, this will produce the results you want:

Book1
	A	B	C	D	E	F	G
1	Site ID	Ranked Group	Territory	Stock ID	Group Space Available (cm)	Group Space Used (cm) Desired Result	Formula Result
2	3456	One	One	12345	50	[=E2*2]	100
3	7890	Two	One	23456	50	[=E3*2]	100
4	2345	Three	One	34567	50	{=E4*0]	0
5	5678	Four	One	45678	50	[=E5*1]	50
6	9876	Five	One	56789	50	[=E6*1]	50
7	2345	One	Two	76543	100	[=E7*2]	200
8	6547	Two	Two	87654	100	[=E8*1]	100
9	8745	Three	Two	98765	100	[=E9*1]	100
10	3465	Four	Two	65432	100	[=E10*1]	100
11	3567	Five	Two	54321	100	[=E11*1]	100
Sheet408

Cell Formulas
Range	Formula
G2	=E2*IF(AND(B2="Three",C2="One"),0,IF(AND(B2="Two",C2="Two"),1,IF(OR(B2={"One","Two"}),2,1)))

Formula copied down.