UDF:Applying a formula to individual cells in a range

[kingofaces]

I'm writing a function in VBA where I am entering a range for the variable and calculating from there. The mean of that range is calculated, but for each cell in the range I want to subtract the mean from it and square that value. The result of each cell will be summed.

For instance, if the range variable is set for G2:G10 then I would like to do: (G2-mean)^2 + (G3-mean)^2 +.......(G10-mean)^2 .

The main issue here is that I need to calculate a value for each cell in the range, and create a total from all those results. Any clarification on how to set this up would be appreciated. If anyone knows of a good guide the explains the range value a bit more would be nice too. Everything I find explains how to create a variable as a range, but I've had poor luck finding anything on how those variables specifically can be used. It seems to be the only basic thing I tend to trip over, so any help there is appreciated.

 

[taigovinda]

Hello and welcome to the board!

You really don't need VBA for what you are describing, you can do it with a simple formula like this:

=SUMPRODUCT((G2:G10-AVERAGE(G2:G10))^2)

You can find a great sumproduct() tutorial here: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

If you have more specific questions about using ranges in VBA, I'm sure you can get answers to those here too, though.

 

[mikerickson]

I'm not at statistician, but that looks very close to something like COUNT(G2:G10)*SDEV(G2:G10). (Perhaps COUNT(G2:G10)+1, ? SDEV(G2:G10)^2 ? )

 

[kingofaces]

Mike, it probably looks familiar because it's a least squares calculation, or R2. It's different though because I'm not using the "line of best fit" but rather a line of my choosing in this case y=x. That's why I'm calculating it by hand rather than LINEST or CORREL()^2.

The reason I started off looking to do this in VBA is because it's a bit less complicated due to how the sheets are set up. I have a large number of columns, where one or two variables change in the formula. I was just thinking it would be easier to change the variables in each case with a simple function rather than having to mine through a larger formula to see what needs to be changed. The other issue is that I still run into the issue where I want to look at each cell in a range for other macros I've considered, so I thought this would be a good simple example to clear that issue up.


taigovinda, SUMPRODUCT is an interesting one I haven't look at before, although it isn't giving the same results as the hand calculations. Here's a sample:

G values

193
207
204
205
195
188
196
191
200
192
190

(G-mean)^2 values

11.9338843
111.2066116
56.9338843
73.02479339
2.115702479
71.47933884
0.20661157
29.75206612
12.57024793
19.84297521
41.66115702



G Mean=196.4545455


Sumproduct result =464.1818182

Actual (G-mean)^2 total= 430.7272727

Can Sumproduct really be used if you aren't multiplying two cells? I'm not familiar with it entirely but just at first glance of how it works I'm not quite sure of what the formula that you posted is doing.

 

[kingofaces]

Actually scratch the issue with SUMPRODUCT, it was just a syntax issue it seems. On a second check it worked just fine. =SUMPRODUCT((G2:G12-AVERAGE(G2:G12))^2)

 

[kingofaces]

Now here's my issue. I try using sumproduct, but I'm getting a #Value error:

Function LSideal(Actual As Range, Predicted As Range)

Actualmean = Application.WorksheetFunction.Average(Actual)
Predictedmean = Application.WorksheetFunction.Average(Predicted)

SST = Application.WorksheetFunction.SumProduct((Predicted - Predictedmean) ^ 2)

LSideal = SST

End Function

The error is in SST = Application.WorksheetFunction.SumProduct((Predicted - Predictedmean) ^ 2). It should be equivalent to the actual formula posted a bit ago, except now that we're dealing with variables. The "Actual" variable isn't coming into play yet, so that can be ignored for the time being. Any thoughts? It seems like SumProduct probably isn't liking the variables for some reason.

 

[kingofaces]

A quick bump. I've looked over some topics including Sumproduct as it was mentioned that you can only use arrays not ranges with it? Could this be why the code in the previous post isn't working?

 

[mikerickson]

I get a Type mismatch when subtracting a constant from a range. It seems that
VBA (tries to) calculate the underlined portion before handing it to SUMPRODUCT (Which can handle a number being subtracted from a range)

Application.Sumproduct([U]Range("A1:A10")-1[/U])

You might try:

Function LSideal(Actual As Range, Predicted As Range)

Actualmean = Application.WorksheetFunction.Average(Actual)
Predictedmean = Application.WorksheetFunction.Average(Predicted)

SST = Evaluate("SumProduct((" & Predicted.Address(,,,True) & " - Predictedmean) ^ 2)")

LSideal = SST

End Function

 

[kingofaces]

I get a #Name? error this time around, and everything was copied and pasted directly. I'll see if I can play around with that idea a bit and see what comes up.

 

[kingofaces]

I did some rummaging around on google and couldn't find anything specific that was too helpful. Here's a quick rundown to sum everything up for anyone else browsing through:

=SUMPRODUCT((C2:C12-AVERAGE(C2:C12))^2)

The crux of the UDF right now is that I need to use that formula in VBA or at least reach the same effect. I'm using range variables instead of the example range C2:C12 and that seems to be throwing Sumproduct for a loop.

Function LSideal(Actual As Range, Predicted As Range)

Actualmean = Application.WorksheetFunction.Average(Actual)
Predictedmean = Application.WorksheetFunction.Average(Predicted)

SST = Application.WorksheetFunction.SumProduct((Predicted - Predictedmean) ^ 2)

LSideal = SST

End Function

Anything to get this function to match the original formula when entered into a cell instead of returning an error would put everything else in line to have a working function.