Excel VBA - Calculating average problem
 

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

--
Message posted from http://www.ExcelForum.com

Excel VBA - Calculating average problem
 

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 Molloy
Microsoft Excel MVP
----------------------------------
"Lena5il " wrote in message
...
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 to
use a loop. But I don't know how to write this formula with a variable
inside.

Help me, please,

Lena


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Calculating average problem
 

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" wrote in message
...

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 Molloy
Microsoft Excel MVP
----------------------------------
"Lena5il " wrote in message
...
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 to
use a loop. But I don't know how to write this formula with a variable
inside.

Help me, please,

Lena


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Calculating average problem
 

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



In article <15dMb.71115$hf1.15480@lakeread06,
"JMay" wrote:

'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?

Excel VBA - Calculating average problem
 

I'm guessing that you really wanted:

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


JMay wrote:

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" wrote in message
...

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 Molloy
Microsoft Excel MVP
----------------------------------
"Lena5il " wrote in message
...
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 to
use a loop. But I don't know how to write this formula with a variable
inside.

Help me, please,

Lena


---
Message posted from http://www.ExcelForum.com/




--

Dave Peterson

Excel VBA - Calculating average problem
 

Thanks, all.

I wrote:
Worksheets(2).Cells(row, 2).Value =
WorksheetFunction.Average(Range(Worksheets(1).Cell s(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


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Calculating average problem
 

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


--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
"JMay" wrote in message
news:15dMb.71115$hf1.15480@lakeread06...
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" wrote in message
...

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 Molloy
Microsoft Excel MVP
----------------------------------
"Lena5il " wrote in message
...
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 to
use a loop. But I don't know how to write this formula with a variable
inside.

Help me, please,

Lena


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Calculating average problem
 

Look Again..
"=Sheet3!Average(RC3:RC137)"

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

Thanks To all
JMay


"Patrick Molloy" wrote in message
...
Looks fine
make sure that you have a "sheet3" or a "sheet1"


--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
"JMay" wrote in message
news:15dMb.71115$hf1.15480@lakeread06...
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" wrote in message
...

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 Molloy
Microsoft Excel MVP
----------------------------------
"Lena5il " wrote in message
...
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
to
use a loop. But I don't know how to write this formula with a
variable
inside.

Help me, please,

Lena


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Calculating average problem
 

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

-----Original Message-----
Look Again..
"=Sheet3!Average(RC3:RC137)"

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

Thanks To all
JMay


"Patrick Molloy" wrote in message
...
Looks fine
make sure that you have a "sheet3" or a "sheet1"


--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
"JMay" wrote in message
news:15dMb.71115$hf1.15480@lakeread06...
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" wrote in
message
...

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 Molloy
Microsoft Excel MVP
----------------------------------
"Lena5il "
wrote in message
...
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
to
use a loop. But I don't know how to write this
formula with a
variable
inside.

Help me, please,

Lena


---
Message posted from http://www.ExcelForum.com/









.