Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Average | Excel Discussion (Misc queries) | |||
Excel Calculating problem | Excel Discussion (Misc queries) | |||
microsoft excel 2000 calculating problem | Excel Discussion (Misc queries) | |||
Calculating the average | Excel Discussion (Misc queries) | |||
Formula for calculating a rolling 12 month average in excel? | Excel Discussion (Misc queries) |