# VBA array function help needed

#### vbaLost

##### New Member
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"))}<>

Last edited:

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.

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

I think there's still something missing.

What in words do you want to do exactly?

What are you summing?

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

Replies
24
Views
985
Replies
5
Views
306
Replies
7
Views
319
Replies
0
Views
188
Replies
3
Views
227

1,203,686
Messages
6,056,736
Members
444,887
Latest member
cvcc_wt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back