Create a variable conditioned on two others

[daniel3141]

Inmay data I have two columns: organisation Code and flag, I want to create a newvariable based on these two. The flag column contains either 0 or 1.

Thesame organisation code can appear many times in the organisation code column, but if there areone or more examples of an organisation code having the number 1 in the flagcolumn, I want the new variable to be 1 in all cases for this organisation even ifthe same organisation is followed by 0 in the flag column in some cases.

OPX 0
OPX 0
OPX 1
OPX 0

So in the above example, the new variable will be a 1 for all cases of organisation code OPX, because there is at least one example of this organisation being followed by 1 in the flag column.

Doesanyone know how to do this within a formula?<o></o>

 

[ArmstrongN]

I would use a countif formula in a header cell on the flag column - so if it is column B - countif($B:$B,1) - which will count all instances of the value 1
Then I'd set a single hidden cell using an if statement. So if your countif formula is in C1 I'd use if(B1>0,1,0)
Then set all values in your third variable column to refer to this single cell as they'll all be either 0 or 1 (if I've understood you correctly)

 

[ArmstrongN]

actually - don't even need countif - just a count would do

 

[daniel3141]

Thank you!