Excel VBA - Calculating average problem

  • Thread starter Thread starter Lena5il
  • Start date Start date
L

Lena5il

Hi all,

I need to calculate an average in VBA excel.
I know only this form of calculation:
Worksheets(2).Cells(3, 2).Value = "=Average(Sheet1!C3:C137)"

But I need to calculate this for few columns and rows, hence I want t
use a loop. But I don't know how to write this formula with a variabl
inside.

Help me, please,

Len
 
Sub PutFormula()
Range("B3:B25").FormulaR1C1 = "=Average(RC3:RC137)"
End Sub

Sub PutFormula2()
Worksheets("Sheet1").Range("B3:B25").FormulaR1C1 =
"=Sheet3!Average(RC3:RC137)"
End Sub


This procedure places the appropriate formula into each cell in the range
B3:B25
No loop required.
The first simply uses the active sheet.
The second places th eformula on a sheet that does not need to be the active
sheet....and I also set the formula to point to a different sheet
 
Patrick:

I've been practicing on the example you gave the OP, but right now I'm
getting
a R/T error 1004 when running:

Sub PutMultiPartFormula()
Worksheets("Sheet3").Range("A1").FormulaR1C1 =
"=Sheet1!Average(R1C2:R10C2)"
End Sub

Can you offer assistance as to why this might be happening?
TIA,
JMay



Patrick Molloy said:
Sub PutFormula()
Range("B3:B25").FormulaR1C1 = "=Average(RC3:RC137)"
End Sub

Sub PutFormula2()
Worksheets("Sheet1").Range("B3:B25").FormulaR1C1 =
"=Sheet3!Average(RC3:RC137)"
End Sub


This procedure places the appropriate formula into each cell in the range
B3:B25
No loop required.
The first simply uses the active sheet.
The second places th eformula on a sheet that does not need to be the active
sheet....and I also set the formula to point to a different sheet
Click to expand...
 
Your formula syntax is incorrect. Try:

Sub PutMultiPartFormula()
Worksheets("Sheet3").Range("A1").FormulaR1C1 = _
"=Average(Sheet1!R1C2:R10C2)"
End Sub

or

Sub PutMultiPartFormula()
Worksheets("Sheet3").Range("A1").Formula = _
"=Average(Sheet1!B1:B10)"
End Sub
 
I'm guessing that you really wanted:

Sub PutMultiPartFormula()
Worksheets("Sheet3").Range("A1").FormulaR1C1 _
= "=Average(sheet1!R1C2:R10C2)"
End Sub

Patrick:

I've been practicing on the example you gave the OP, but right now I'm
getting
a R/T error 1004 when running:

Sub PutMultiPartFormula()
Worksheets("Sheet3").Range("A1").FormulaR1C1 =
"=Sheet1!Average(R1C2:R10C2)"
End Sub

Can you offer assistance as to why this might be happening?
TIA,
JMay
Click to expand...
 
Thanks, all.

I wrote:
Worksheets(2).Cells(row, 2).Value =
WorksheetFunction.Average(Range(Worksheets(1).Cells(3, col),
Worksheets(1).Cells(137, col)))

and it is working.

But how can I calculate the average and stdev of the array?

I saw in some references something like:

Div arr(1 To 10) As Double

avg = System.StDev(arr) or something like that, but it doesn't work.

What is the right form to do this?


Lena
 
Looks fine
make sure that you have a "sheet3" or a "sheet1"


--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
JMay said:
Patrick:

I've been practicing on the example you gave the OP, but right now I'm
getting
a R/T error 1004 when running:

Sub PutMultiPartFormula()
Worksheets("Sheet3").Range("A1").FormulaR1C1 =
"=Sheet1!Average(R1C2:R10C2)"
End Sub

Can you offer assistance as to why this might be happening?
TIA,
JMay
Click to expand...
 
Look Again..
"=Sheet3!Average(RC3:RC137)"

s/b "=Average(Sheet3!RC3:RC137)"

Thanks To all
JMay
 
that's as per your question & my answer, however, the
last question set th eaverage on an area rather than a
column or row , and I didn't see whythere was an error

Patrick
 
Back
Top