CODEWITHSUNDEEP
excelexcel-formulaarray-formulas
ChemEnger
ChemEnger

Reputation: 149

Excel [Standard Deviation] of range filtered against another range

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.

Upvotes: 0

Views: 385

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

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.

Upvotes: 1

Related Questions