VBA array function help needed

[vbaLost]

I am trying to write the following function using VBA. My data is for example in cells C6 to F6.



{=SUM(IF(C6:F6AVERAGE(C6:F6),(C6:F6-AVERAGE(C6:F6))^2,"no"))}<?XML:NAMESPACE PREFIX = AVERAGE(C6 /><AVERAGE(C6:F6),(C6:F6-AVERAGE(C6:F6))^2,"no"))}< p>
<AVERAGE(C6:F6),(C6:F6-AVERAGE(C6:F6))^2,"no"))}< p>
I have come up with (but don't know how to make it an array formula):

Function ifSD(TheRange As Variant)

tst = Evaluate(TheRange < Application.WorksheetFunction.Average(TheRange))

s2 = (TheRange - Application.WorksheetFunction.Average(TheRange)) ^ 2

s3 = "no"
If tst = True Then
ifSD = application.worksheetfunction.sum(s2)
Else
ifSD = s3
End If
End Function
</AVERAGE(C6:F6),(C6:F6-AVERAGE(C6:F6))^2,"no"))}<>
</AVERAGE(C6:F6),(C6:F6-AVERAGE(C6:F6))^2,"no"))}<>

 

[Norie]

Are you sure you can't do this using worksheet functions?

Not all the formula came through but I think you want to do something like sum the values in your range that are less than the average.

I could be totally wrong of course.

 

[vbaLost]

No I don't think any worksheet function can do this. Yes that is basically what i'm trying to do. There should be a "<" just before the first average function

 

[Norie]

I think there's still something missing.

What in words do you want to do exactly?

What are you summing?

 

[vbaLost]

I am trying to sum the square of the difference between each cell in the range, and the average value in that range. Only if the value in the cells are less than the average. Hope this makes sense