Can this rule be applied in a sigle cell function?

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>
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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!
 
Upvote 0
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))
 
Upvote 0
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!
 
Upvote 0
try this


Book1
ABCDEF
1Site IDRanked GroupTerritoryStock IDGroup Space Available (cm)Group Space Used (cm) Desired Result
23456111234550100
37890212345650100
423453134567500
5567841456785050
6987651567895050
723451276543100200
865472287654100100
987453298765100100
1034654265432100100
1135675254321100100
Sheet1
Cell Formulas
RangeFormula
F2=CHOOSE(C2,CHOOSE(B2,2,2,0,1,1),CHOOSE(B2,2,1,1,1,1))*E2
 
Upvote 0
Hi,

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


Book1
ABCDEFG
1Site IDRanked GroupTerritoryStock IDGroup Space Available (cm)Group Space Used (cm) Desired ResultFormula Result
23456OneOne1234550[=E2*2]100
37890TwoOne2345650[=E3*2]100
42345ThreeOne3456750{=E4*0]0
55678FourOne4567850[=E5*1]50
69876FiveOne5678950[=E6*1]50
72345OneTwo76543100[=E7*2]200
86547TwoTwo87654100[=E8*1]100
98745ThreeTwo98765100[=E9*1]100
103465FourTwo65432100[=E10*1]100
113567FiveTwo54321100[=E11*1]100
Sheet408
Cell Formulas
RangeFormula
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.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top