array within an array to discount selected items from formula

Hi there,

i have a formula that current looks like this:
={MAX(IF((AreaCode=$T$3)*(SampleType=$C$1)*(ParamCode=$B7)*OR(LocationCode<>{"GW37","GW33","GW41","GW31"})>0,Unxtab_RAWData!$R$2:$R$34999))}

the idea being is searches through the data which has ~35000 rows, and provides the max for a column when in accordance with the criteria. It worked fine before, however the bit I have just added in is the "LocationCode" selection highlighted in bold, as i need to remove some points (the ones with location IDs GW37,GW33,GW41,GW31). However, this bit isn't working and the values for these locations are still being summarised (or maxed) in by data.

does anyone have an idea of where i'm going wrong?

thanks

Hi - no afraid that doesn't work. Each line will only have one of the IDs present. So one row may have GW41 (it wont have any of the other IDs). the IDs are all in one column.

Originally Posted by Hub101

Hi - no afraid that doesn't work. Each line will only have one of the IDs present. So one row may have GW41 (it wont have any of the other IDs). the IDs are all in one column.

Did you try it?

The OR part that you have means "If LocationCode <> GW37 OR LocationCode <> GW33 OR....."

This will always be TRUE. LocationCode has one value so will always be not equal to at least three of the values in the list.

The logic you want is that LocationCode is not equal to any of them, which is "If LocationCode <> GW37 AND LocationCode <> GW33 AND....."

If you still don't have a solution, can I ask that you attach your file so we can see what your data looks like? How is LocationCode defined? Even if you blank out the data leaving headers, we can see what the layout is.