Firstly - no VBA if it can be helped please!
I am trying to get the Standard Deviation of a range of cells, filtered against a second range (where I want to list specific exclusions.
I have the data in a table with columns of Batch No, Date, Weight and can easily filter for a range of dates with:
=STDEV.S(IF(Table1[Date]>=Start_Date,IF(Table1[Date]<=End_Date,Table1[Weight])))
(Entered as an array formula) I can also filter out a specific Batch Number like:
=STDEV.S(IF(Table1[Date]>=Start_Date,IF(Table1[Date]<=End_Date,IF(Table1[Batch No]<>7097,Table1[Weight]))))
What I would like to do is have a list of more than one Batch No to exclude, adding to this list as needed. I have tried to do this by listing the exclusions in a range and having a cell as an array of this range, then using this cell as the filter, e.g. C2 =7097, C3 =7085, D3 {=C2:C3} and pointing the above formula to D3 instead of just 7097 but no luck.
Any help gratefully received please.
Use Match:
=STDEV.S(IF(Table1[Date]>=Start_Date,IF(Table1[Date]<=End_Date,IF(ISERROR(MATCH(Table1[Batch No],$C$2:$C$3,0)),Table1[Weight]))))
Depending on one's version this may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.