Calculate standard deviation across multiple sheets with partial match

[Goldcar400]

Hello

I want to calculate standard deviation across multiple sheets on negative data only.
Data range is c9:j9 on both sheets Aggregate1 and Aggregate2.
When there is a partial match in range c9 to j9 for negative numbers,
calculate std dev for negative numbers only on both sheets. Ignore positive numbers in range.
I can match negative numbers for sheet Aggregate1 but do not know how
to match negative numbers for sheet Aggregate2 in aggregate function.
I am not restricted to using aggregate method.
There could be a #N/A in data range but looks like option "6" in aggregate
function can handle #N/A

See attached file

Regards
Tony

 

[p45cal]

After messing about with tricks like:
CHOOSE({1;2},C9:J9,Aggregate2!C9:J9)
to bring noncontiguous ranges into worksheet functions like AGGREGATE and STDEV_S, the presence of that #N/A in your data and only wanting to take into account negative numbers made the maintenance/alteration of the formula very unwieldy.
Since you supplied a macro-enabled workbook I went for a user defined function (UDF) approach which makes things much easier for the user with a formula like:
=mySTDEVS(C9:J9,Aggregate2!C9:J9)
See attached.
The UDF code:

Function mySTDEVS(ParamArray args())
ReDim Results(1 To 1)
i = 0
For Each Rng In args
RngVals = Rng.Value
  For Each v In RngVals
    If Not IsError(v) Then
      If v < 0 Then
        i = i + 1
        ReDim Preserve Results(1 To i)
        Results(i) = v
      End If
    End If
  Next v
Next Rng
mySTDEVS = Application.StDev_S(Results)
End Function

which may need a tweak or two.

 

[Goldcar400]

Thanks P45cal

If I remove #N/A from being in my data then what would your cell formula be? I can filter my data to prevent #N/A from being present. I am curious to these creative solutions. (After seeing what excel can do on this forum, I am impressed with Excel!!)

Regards and thanks in advance
Tony

 

[p45cal]

If there were only numbers and blank cells in the ranges concerned then the likes of:
=STDEV.S(IF(CHOOSE({1;2},C9:I9,Aggregate2!C9:I9)<0,CHOOSE({1;2},C9:I9,Aggregate2!C9:I9)))
(array-entered meaning Ctrl+Shift+Enter, not just Enter)