=STDEV(((B1:B10)=(C1:C10))*(F1:F10))validate formula using CTRL+SHIFT+ENTER
Function stdevBequalsC(plage As Range) Dim c As Range Dim chaine As String Dim cpt As Integer For Each c In plage If Range("B" & c.Row) = Range("C" & c.Row) Then cpt = cpt + 1 If cpt = 1 Then chaine = "F" & c.Row cont = c.Row Else If cont = c.Row - 1 Then chaine = chaine & ":F" & c.Row Else chaine = chaine & ",F" & c.Row End If End If End If Next c stdevBequalsC = WorksheetFunction.StDev(Range(chaine)) End Function
DON'T MISS
Calculate the sum of column F by:
=IF(A5="x",SUMPRODUCT((B5:B50=B5)*(C5:C50=C5)*(F5:F50)),"")
Then to get the count of rows that are the same in B & C by:
=IF(A5="x",SUMPRODUCT((B5:B50=B5)*(C5:C50=C5)),"")
These can be hidden columns, and then just have a simple formula in the next to divide the sum by the count.
Now, the harder question is - can I do something similar to get the Standard Dev (STDEV) of the same vaules? Again, only when B & C are identical - need STDEV of column F.
Here's the data I am currently working with as I test this (first row is coumn headings):
B C D E F
Sample ID: Sintering Conditions: Starting diameter(mm): Measured diameter(mm): % Original Size:
15 Mil Cast 1375/1 1.5 1.153 23.13333%
15 Mil Cast 1375/1 1.5 1.154 23.06667%
15 Mil Cast 1375/1 1.5 1.155 23.00000%
15 Mil Cast 1375/1 1.5 1.16 22.66667%
30 Mil Cast 1350/0.5 1.5 1.0 33.33333%
30 Mil Cast 1350/0.5 1.5 1.1 26.66667%
30 Mil Cast 1350/0.5 1.5 1.3 13.33333%
30 Mil Cast 1350/0.5 1.5 1.133 24.46667%