Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find last row with Data, avg last 30, show result on row
Hi,
I've been attempting to write a macro to do this and received prior feedback. First response was I did not need a macro, I can use the formula: =IF(COUNT(A10:A1010),AVERAGE(A1010:INDEX(A10:A1010 ,INDEX(LARGE((A10:A1010<"")*ROW(A10:A1010),31),0) )),0) Even though this works, I can't make heads or tails of this. When I try to break this down into smaller functions, it falls apart What is going on by multiplying ((A10:A1010<")*Row(A10:1010),31)0) Using Nested indexes? What does A1010<"" mean? What are some rules for cell references? How do I make a cell reference a variable? Can somone please dicipher this... for me? Also, what if the number of data points collected has not reached 30 yet? Will this function fail? It does not appear to be failing when I test it with just 3 values, but I dont' really understand specifically what the function is doing. __________________________________________________ __________________ The second response was a macro. Sub Math() Dim lastRow As Long Dim sh As Worksheet For Each sh In Worksheets sh.Activate With sh lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row ..Cells(lastRow, "B").FormulaR1C1 _ = "=average(r[-1]c:r[-30]c)" End With Next sh End Sub __________________________________________________ __________________ There are a few things that I would like it to do differently. I want the macro to (starting from Row 12) find the last row and then average the last 30 entities, and post the result on Row 10. I only need to run the macro on a specific worksheet, not multiple sheets. I want to start the macro running once I get 30 sets of data Also, data is recorded in column's A:AZ, and I have to perform this function on every column throughout the entire spectrum. Every so often, I hit a button to copy and paste special the values to another spreadsheet, so this macro has to continue to run while I run another macro at random intervals. I would like to increase my data field from 1200 points to 12,000 points so I can run my test for anywhere from 1 hour (data collected every 3 seconds) to 10 hours __________________________________________________ __________________ Also, in the statement "=average(r[-1]c:r[-30]c)", is there a way to replace the -1 and -30 with counters to represent the row index number like "x" and "y", where "X"=50 and "y" =80. How do I get the macro to put the answer in a specific Row that I activate versus ..Cells(lastRow,"A"). I want the answer to be displayed on Cells("A12") |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find last row with Data, avg last 30, show result on row
Try this code. Don't know what answer you want in A12? I put the average of
the last 30 entries in row 10 for each column. Sub CalcAverage() StartRow = 12 'loop forever Do While (1) 'Get Last row LastRow = Range("A" & Rows.Count).End(xlUp).Row 'only collect data when 30 rows are reached If (LastRow - StartRow) + 1 = 30 Then 'perform averages on last 30 rows FirstRow = LastRow - 30 + 1 For ColCount = 1 To Range("AZ10").Column Set AverageRange = _ Range(Cells(FirstRow, ColCount), Cells(LastRow, ColCount)) ColAverage = WorksheetFunction.Average(AverageRange) Cells(10, ColCount) = ColAverage Next ColCount End If Application.Wait Now() + Second(10) Loop End Sub "soccerdav2003" wrote: Hi, I've been attempting to write a macro to do this and received prior feedback. First response was I did not need a macro, I can use the formula: =IF(COUNT(A10:A1010),AVERAGE(A1010:INDEX(A10:A1010 ,INDEX(LARGE((A10:A1010<"")*ROW(A10:A1010),31),0) )),0) Even though this works, I can't make heads or tails of this. When I try to break this down into smaller functions, it falls apart What is going on by multiplying ((A10:A1010<")*Row(A10:1010),31)0) Using Nested indexes? What does A1010<"" mean? What are some rules for cell references? How do I make a cell reference a variable? Can somone please dicipher this... for me? Also, what if the number of data points collected has not reached 30 yet? Will this function fail? It does not appear to be failing when I test it with just 3 values, but I dont' really understand specifically what the function is doing. __________________________________________________ __________________ The second response was a macro. Sub Math() Dim lastRow As Long Dim sh As Worksheet For Each sh In Worksheets sh.Activate With sh lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(lastRow, "B").FormulaR1C1 _ = "=average(r[-1]c:r[-30]c)" End With Next sh End Sub __________________________________________________ __________________ There are a few things that I would like it to do differently. I want the macro to (starting from Row 12) find the last row and then average the last 30 entities, and post the result on Row 10. I only need to run the macro on a specific worksheet, not multiple sheets. I want to start the macro running once I get 30 sets of data Also, data is recorded in column's A:AZ, and I have to perform this function on every column throughout the entire spectrum. Every so often, I hit a button to copy and paste special the values to another spreadsheet, so this macro has to continue to run while I run another macro at random intervals. I would like to increase my data field from 1200 points to 12,000 points so I can run my test for anywhere from 1 hour (data collected every 3 seconds) to 10 hours __________________________________________________ __________________ Also, in the statement "=average(r[-1]c:r[-30]c)", is there a way to replace the -1 and -30 with counters to represent the row index number like "x" and "y", where "X"=50 and "y" =80. How do I get the macro to put the answer in a specific Row that I activate versus .Cells(lastRow,"A"). I want the answer to be displayed on Cells("A12") |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find last row with Data, avg last 30, show result on
Thanks for yor feedback Joel. You'll have to forgive my ignorance when it
comes to programming, but can you explain a few syntatic things for me? What is the (1) in the While(1) referring to? In the statement: Range("A" & Rows.Count).End(xlUp).Row I'm assuming the "A"&rows.count is counting all the rows in column A What is the syntax &rows.count mean? I'm not familiar with the Worksheet function command, but I'm assuming the statement "ColAverage = WorksheetFunction.Average(AverageRange)" is averaging the values in what you call the AverageRange, which is range of cells between the first row and last row for each column being averaged? "Joel" wrote: Try this code. Don't know what answer you want in A12? I put the average of the last 30 entries in row 10 for each column. Sub CalcAverage() StartRow = 12 'loop forever Do While (1) 'Get Last row LastRow = Range("A" & Rows.Count).End(xlUp).Row 'only collect data when 30 rows are reached If (LastRow - StartRow) + 1 = 30 Then 'perform averages on last 30 rows FirstRow = LastRow - 30 + 1 For ColCount = 1 To Range("AZ10").Column Set AverageRange = _ Range(Cells(FirstRow, ColCount), Cells(LastRow, ColCount)) ColAverage = WorksheetFunction.Average(AverageRange) Cells(10, ColCount) = ColAverage Next ColCount End If Application.Wait Now() + Second(10) Loop End Sub "soccerdav2003" wrote: Hi, I've been attempting to write a macro to do this and received prior feedback. First response was I did not need a macro, I can use the formula: =IF(COUNT(A10:A1010),AVERAGE(A1010:INDEX(A10:A1010 ,INDEX(LARGE((A10:A1010<"")*ROW(A10:A1010),31),0) )),0) Even though this works, I can't make heads or tails of this. When I try to break this down into smaller functions, it falls apart What is going on by multiplying ((A10:A1010<")*Row(A10:1010),31)0) Using Nested indexes? What does A1010<"" mean? What are some rules for cell references? How do I make a cell reference a variable? Can somone please dicipher this... for me? Also, what if the number of data points collected has not reached 30 yet? Will this function fail? It does not appear to be failing when I test it with just 3 values, but I dont' really understand specifically what the function is doing. __________________________________________________ __________________ The second response was a macro. Sub Math() Dim lastRow As Long Dim sh As Worksheet For Each sh In Worksheets sh.Activate With sh lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(lastRow, "B").FormulaR1C1 _ = "=average(r[-1]c:r[-30]c)" End With Next sh End Sub __________________________________________________ __________________ There are a few things that I would like it to do differently. I want the macro to (starting from Row 12) find the last row and then average the last 30 entities, and post the result on Row 10. I only need to run the macro on a specific worksheet, not multiple sheets. I want to start the macro running once I get 30 sets of data Also, data is recorded in column's A:AZ, and I have to perform this function on every column throughout the entire spectrum. Every so often, I hit a button to copy and paste special the values to another spreadsheet, so this macro has to continue to run while I run another macro at random intervals. I would like to increase my data field from 1200 points to 12,000 points so I can run my test for anywhere from 1 hour (data collected every 3 seconds) to 10 hours __________________________________________________ __________________ Also, in the statement "=average(r[-1]c:r[-30]c)", is there a way to replace the -1 and -30 with counters to represent the row index number like "x" and "y", where "X"=50 and "y" =80. How do I get the macro to put the answer in a specific Row that I activate versus .Cells(lastRow,"A"). I want the answer to be displayed on Cells("A12") |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find last row with Data, avg last 30, show result on
1) What is the (1) in the While(1) referring to?
The 1 in a while means to loop forever. You can replace the 1 with any test you want 2) Range("A" & Rows.Count).End(xlUp).Row Rows.Count is the maximum number or rows in a worksheet which for excel 2003 is 65536. For excel it is larger. This statment means to go to the last row in column A and move up towards row 1 until you find a cell with data. The problem with xldown is if you have blank rows of data xldown stops at the last cell of data below the empty row. 3) WorksheetFunction.Average(AverageRange) VBA doesn't have an average function so yo ucan use the worksheet function instead. 4) AverageRange, which is range of cells between the first row and last row for each column being averaged? No, just the last 30 rows. 'Get Last row LastRow = Range("A" & Rows.Count).End(xlUp).Row This statement subtracts 30 from the last row ----------------------------------------------------- FirstRow = LastRow - 30 + 1 ------------------------------------------------------ This statemenrt is only looking at the last 30 rows For ColCount = 1 To Range("AZ10").Column Set AverageRange = _ Range(Cells(FirstRow, ColCount), Cells(LastRow, ColCount)) "soccerdav2003" wrote: Thanks for yor feedback Joel. You'll have to forgive my ignorance when it comes to programming, but can you explain a few syntatic things for me? What is the (1) in the While(1) referring to? In the statement: Range("A" & Rows.Count).End(xlUp).Row I'm assuming the "A"&rows.count is counting all the rows in column A What is the syntax &rows.count mean? I'm not familiar with the Worksheet function command, but I'm assuming the statement "ColAverage = WorksheetFunction.Average(AverageRange)" is averaging the values in what you call the AverageRange, which is range of cells between the first row and last row for each column being averaged? "Joel" wrote: Try this code. Don't know what answer you want in A12? I put the average of the last 30 entries in row 10 for each column. Sub CalcAverage() StartRow = 12 'loop forever Do While (1) 'Get Last row LastRow = Range("A" & Rows.Count).End(xlUp).Row 'only collect data when 30 rows are reached If (LastRow - StartRow) + 1 = 30 Then 'perform averages on last 30 rows FirstRow = LastRow - 30 + 1 For ColCount = 1 To Range("AZ10").Column Set AverageRange = _ Range(Cells(FirstRow, ColCount), Cells(LastRow, ColCount)) ColAverage = WorksheetFunction.Average(AverageRange) Cells(10, ColCount) = ColAverage Next ColCount End If Application.Wait Now() + Second(10) Loop End Sub "soccerdav2003" wrote: Hi, I've been attempting to write a macro to do this and received prior feedback. First response was I did not need a macro, I can use the formula: =IF(COUNT(A10:A1010),AVERAGE(A1010:INDEX(A10:A1010 ,INDEX(LARGE((A10:A1010<"")*ROW(A10:A1010),31),0) )),0) Even though this works, I can't make heads or tails of this. When I try to break this down into smaller functions, it falls apart What is going on by multiplying ((A10:A1010<")*Row(A10:1010),31)0) Using Nested indexes? What does A1010<"" mean? What are some rules for cell references? How do I make a cell reference a variable? Can somone please dicipher this... for me? Also, what if the number of data points collected has not reached 30 yet? Will this function fail? It does not appear to be failing when I test it with just 3 values, but I dont' really understand specifically what the function is doing. __________________________________________________ __________________ The second response was a macro. Sub Math() Dim lastRow As Long Dim sh As Worksheet For Each sh In Worksheets sh.Activate With sh lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(lastRow, "B").FormulaR1C1 _ = "=average(r[-1]c:r[-30]c)" End With Next sh End Sub __________________________________________________ __________________ There are a few things that I would like it to do differently. I want the macro to (starting from Row 12) find the last row and then average the last 30 entities, and post the result on Row 10. I only need to run the macro on a specific worksheet, not multiple sheets. I want to start the macro running once I get 30 sets of data Also, data is recorded in column's A:AZ, and I have to perform this function on every column throughout the entire spectrum. Every so often, I hit a button to copy and paste special the values to another spreadsheet, so this macro has to continue to run while I run another macro at random intervals. I would like to increase my data field from 1200 points to 12,000 points so I can run my test for anywhere from 1 hour (data collected every 3 seconds) to 10 hours __________________________________________________ __________________ Also, in the statement "=average(r[-1]c:r[-30]c)", is there a way to replace the -1 and -30 with counters to represent the row index number like "x" and "y", where "X"=50 and "y" =80. How do I get the macro to put the answer in a specific Row that I activate versus .Cells(lastRow,"A"). I want the answer to be displayed on Cells("A12") |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find last row with Data, avg last 30, show result on
Hey Joel,
This isn't working. It's getting hung up on ColAverage. All I did was copy your code into my procedure form. "Joel" wrote: Try this code. Don't know what answer you want in A12? I put the average of the last 30 entries in row 10 for each column. Sub CalcAverage() StartRow = 12 'loop forever Do While (1) 'Get Last row LastRow = Range("A" & Rows.Count).End(xlUp).Row 'only collect data when 30 rows are reached If (LastRow - StartRow) + 1 = 30 Then 'perform averages on last 30 rows FirstRow = LastRow - 30 + 1 For ColCount = 1 To Range("AZ10").Column Set AverageRange = _ Range(Cells(FirstRow, ColCount), Cells(LastRow, ColCount)) ColAverage = WorksheetFunction.Average(AverageRange) Cells(10, ColCount) = ColAverage Next ColCount End If Application.Wait Now() + Second(10) Loop End Sub "soccerdav2003" wrote: Hi, I've been attempting to write a macro to do this and received prior feedback. First response was I did not need a macro, I can use the formula: =IF(COUNT(A10:A1010),AVERAGE(A1010:INDEX(A10:A1010 ,INDEX(LARGE((A10:A1010<"")*ROW(A10:A1010),31),0) )),0) Even though this works, I can't make heads or tails of this. When I try to break this down into smaller functions, it falls apart What is going on by multiplying ((A10:A1010<")*Row(A10:1010),31)0) Using Nested indexes? What does A1010<"" mean? What are some rules for cell references? How do I make a cell reference a variable? Can somone please dicipher this... for me? Also, what if the number of data points collected has not reached 30 yet? Will this function fail? It does not appear to be failing when I test it with just 3 values, but I dont' really understand specifically what the function is doing. __________________________________________________ __________________ The second response was a macro. Sub Math() Dim lastRow As Long Dim sh As Worksheet For Each sh In Worksheets sh.Activate With sh lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(lastRow, "B").FormulaR1C1 _ = "=average(r[-1]c:r[-30]c)" End With Next sh End Sub __________________________________________________ __________________ There are a few things that I would like it to do differently. I want the macro to (starting from Row 12) find the last row and then average the last 30 entities, and post the result on Row 10. I only need to run the macro on a specific worksheet, not multiple sheets. I want to start the macro running once I get 30 sets of data Also, data is recorded in column's A:AZ, and I have to perform this function on every column throughout the entire spectrum. Every so often, I hit a button to copy and paste special the values to another spreadsheet, so this macro has to continue to run while I run another macro at random intervals. I would like to increase my data field from 1200 points to 12,000 points so I can run my test for anywhere from 1 hour (data collected every 3 seconds) to 10 hours __________________________________________________ __________________ Also, in the statement "=average(r[-1]c:r[-30]c)", is there a way to replace the -1 and -30 with counters to represent the row index number like "x" and "y", where "X"=50 and "y" =80. How do I get the macro to put the answer in a specific Row that I activate versus .Cells(lastRow,"A"). I want the answer to be displayed on Cells("A12") |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find last row with Data, avg last 30, show result on
Here is a better way of doing it. Call the function once. It will call
itself every 3 seconds Sub CalcAverage() StartRow = 12 'loop forever 'Get Last row LastRow = Range("A" & Rows.Count).End(xlUp).Row 'only collect data when 30 rows are reached If (LastRow - StartRow) + 1 = 30 Then 'perform averages on last 30 rows FirstRow = LastRow - 30 + 1 For ColCount = 1 To Range("AZ10").Column Set AverageRange = _ Range(Cells(FirstRow, ColCount), _ Cells(LastRow, ColCount)) ColAverage = WorksheetFunction.Average(AverageRange) Cells(10, ColCount) = ColAverage Next ColCount End If Application.OnTime Now + _ TimeValue("00:00:03"), "CalcAverage" End Sub "soccerdav2003" wrote: Hey Joel, This isn't working. It's getting hung up on ColAverage. All I did was copy your code into my procedure form. "Joel" wrote: Try this code. Don't know what answer you want in A12? I put the average of the last 30 entries in row 10 for each column. Sub CalcAverage() StartRow = 12 'loop forever Do While (1) 'Get Last row LastRow = Range("A" & Rows.Count).End(xlUp).Row 'only collect data when 30 rows are reached If (LastRow - StartRow) + 1 = 30 Then 'perform averages on last 30 rows FirstRow = LastRow - 30 + 1 For ColCount = 1 To Range("AZ10").Column Set AverageRange = _ Range(Cells(FirstRow, ColCount), Cells(LastRow, ColCount)) ColAverage = WorksheetFunction.Average(AverageRange) Cells(10, ColCount) = ColAverage Next ColCount End If Application.Wait Now() + Second(10) Loop End Sub "soccerdav2003" wrote: Hi, I've been attempting to write a macro to do this and received prior feedback. First response was I did not need a macro, I can use the formula: =IF(COUNT(A10:A1010),AVERAGE(A1010:INDEX(A10:A1010 ,INDEX(LARGE((A10:A1010<"")*ROW(A10:A1010),31),0) )),0) Even though this works, I can't make heads or tails of this. When I try to break this down into smaller functions, it falls apart What is going on by multiplying ((A10:A1010<")*Row(A10:1010),31)0) Using Nested indexes? What does A1010<"" mean? What are some rules for cell references? How do I make a cell reference a variable? Can somone please dicipher this... for me? Also, what if the number of data points collected has not reached 30 yet? Will this function fail? It does not appear to be failing when I test it with just 3 values, but I dont' really understand specifically what the function is doing. __________________________________________________ __________________ The second response was a macro. Sub Math() Dim lastRow As Long Dim sh As Worksheet For Each sh In Worksheets sh.Activate With sh lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(lastRow, "B").FormulaR1C1 _ = "=average(r[-1]c:r[-30]c)" End With Next sh End Sub __________________________________________________ __________________ There are a few things that I would like it to do differently. I want the macro to (starting from Row 12) find the last row and then average the last 30 entities, and post the result on Row 10. I only need to run the macro on a specific worksheet, not multiple sheets. I want to start the macro running once I get 30 sets of data Also, data is recorded in column's A:AZ, and I have to perform this function on every column throughout the entire spectrum. Every so often, I hit a button to copy and paste special the values to another spreadsheet, so this macro has to continue to run while I run another macro at random intervals. I would like to increase my data field from 1200 points to 12,000 points so I can run my test for anywhere from 1 hour (data collected every 3 seconds) to 10 hours __________________________________________________ __________________ Also, in the statement "=average(r[-1]c:r[-30]c)", is there a way to replace the -1 and -30 with counters to represent the row index number like "x" and "y", where "X"=50 and "y" =80. How do I get the macro to put the answer in a specific Row that I activate versus .Cells(lastRow,"A"). I want the answer to be displayed on Cells("A12") |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find last row with Data, avg last 30, show result on
Hi Joel,
I got everything to work...pretty much. There's just one more thing I'm trying to do now and I'm having trouble getting it to work. I want to export average to another spreadsheet and have it index on the spreadsheet so whenever I run the macro it will record the average of all the values in the row and write to a another spreadsheet on a specific sheet and index to the next row on that sheet so I'm appending the exported data, not overwriting it. Here's how I want the data to look: Average Values for Increasing Flow: Volume Power q" Pressure Flow Psaturation 170 200 50 0.090 150 3.03 170 200 50 0.138 200 3.26 170 200 50 0.100 250 3.80 170 200 50 0.153 300 3.64 "Joel" wrote: Here is a better way of doing it. Call the function once. It will call itself every 3 seconds Sub CalcAverage() StartRow = 12 'loop forever 'Get Last row LastRow = Range("A" & Rows.Count).End(xlUp).Row 'only collect data when 30 rows are reached If (LastRow - StartRow) + 1 = 30 Then 'perform averages on last 30 rows FirstRow = LastRow - 30 + 1 For ColCount = 1 To Range("AZ10").Column Set AverageRange = _ Range(Cells(FirstRow, ColCount), _ Cells(LastRow, ColCount)) ColAverage = WorksheetFunction.Average(AverageRange) Cells(10, ColCount) = ColAverage Next ColCount End If Application.OnTime Now + _ TimeValue("00:00:03"), "CalcAverage" End Sub "soccerdav2003" wrote: Hey Joel, This isn't working. It's getting hung up on ColAverage. All I did was copy your code into my procedure form. "Joel" wrote: Try this code. Don't know what answer you want in A12? I put the average of the last 30 entries in row 10 for each column. Sub CalcAverage() StartRow = 12 'loop forever Do While (1) 'Get Last row LastRow = Range("A" & Rows.Count).End(xlUp).Row 'only collect data when 30 rows are reached If (LastRow - StartRow) + 1 = 30 Then 'perform averages on last 30 rows FirstRow = LastRow - 30 + 1 For ColCount = 1 To Range("AZ10").Column Set AverageRange = _ Range(Cells(FirstRow, ColCount), Cells(LastRow, ColCount)) ColAverage = WorksheetFunction.Average(AverageRange) Cells(10, ColCount) = ColAverage Next ColCount End If Application.Wait Now() + Second(10) Loop End Sub "soccerdav2003" wrote: Hi, I've been attempting to write a macro to do this and received prior feedback. First response was I did not need a macro, I can use the formula: =IF(COUNT(A10:A1010),AVERAGE(A1010:INDEX(A10:A1010 ,INDEX(LARGE((A10:A1010<"")*ROW(A10:A1010),31),0) )),0) Even though this works, I can't make heads or tails of this. When I try to break this down into smaller functions, it falls apart What is going on by multiplying ((A10:A1010<")*Row(A10:1010),31)0) Using Nested indexes? What does A1010<"" mean? What are some rules for cell references? How do I make a cell reference a variable? Can somone please dicipher this... for me? Also, what if the number of data points collected has not reached 30 yet? Will this function fail? It does not appear to be failing when I test it with just 3 values, but I dont' really understand specifically what the function is doing. __________________________________________________ __________________ The second response was a macro. Sub Math() Dim lastRow As Long Dim sh As Worksheet For Each sh In Worksheets sh.Activate With sh lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(lastRow, "B").FormulaR1C1 _ = "=average(r[-1]c:r[-30]c)" End With Next sh End Sub __________________________________________________ __________________ There are a few things that I would like it to do differently. I want the macro to (starting from Row 12) find the last row and then average the last 30 entities, and post the result on Row 10. I only need to run the macro on a specific worksheet, not multiple sheets. I want to start the macro running once I get 30 sets of data Also, data is recorded in column's A:AZ, and I have to perform this function on every column throughout the entire spectrum. Every so often, I hit a button to copy and paste special the values to another spreadsheet, so this macro has to continue to run while I run another macro at random intervals. I would like to increase my data field from 1200 points to 12,000 points so I can run my test for anywhere from 1 hour (data collected every 3 seconds) to 10 hours __________________________________________________ __________________ Also, in the statement "=average(r[-1]c:r[-30]c)", is there a way to replace the -1 and -30 with counters to represent the row index number like "x" and "y", where "X"=50 and "y" =80. How do I get the macro to put the answer in a specific Row that I activate versus .Cells(lastRow,"A"). I want the answer to be displayed on Cells("A12") |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find last row with Data, avg last 30, show result on
I added a name to your Data sheet ("Data") and a Summary Sheet ("Summary").
then I copies row 10 on the data sheet to the Lastrow +1 of the Summary sheet. Sub CalcAverage() StartRow = 12 'loop forever With Sheets("Data") Set CopyRange = .Range("A10:AZ10") 'Get Last row LastRow = .Range("A" & Rows.Count).End(xlUp).Row 'only collect data when 30 rows are reached If (LastRow - StartRow) + 1 = 30 Then 'perform averages on last 30 rows FirstRow = LastRow - 30 + 1 For ColCount = 1 To Range("AZ10").Column Set AverageRange = _ .Range(.Cells(FirstRow, ColCount), _ .Cells(LastRow, ColCount)) ColAverage = WorksheetFunction.Average(AverageRange) .Cells(10, ColCount) = ColAverage Next ColCount End If End With With Sheets("Summary") 'copy averages to new worksheet LastRow = .Range("A" & Rows.Count).End(xlUp).Row CopyRange.Copy Destination:=.Range("A" & (LastRow + 1)) End With Application.OnTime Now + _ TimeValue("00:00:03"), "CalcAverage" End Sub "soccerdav2003" wrote: Hi Joel, I got everything to work...pretty much. There's just one more thing I'm trying to do now and I'm having trouble getting it to work. I want to export average to another spreadsheet and have it index on the spreadsheet so whenever I run the macro it will record the average of all the values in the row and write to a another spreadsheet on a specific sheet and index to the next row on that sheet so I'm appending the exported data, not overwriting it. Here's how I want the data to look: Average Values for Increasing Flow: Volume Power q" Pressure Flow Psaturation 170 200 50 0.090 150 3.03 170 200 50 0.138 200 3.26 170 200 50 0.100 250 3.80 170 200 50 0.153 300 3.64 "Joel" wrote: Here is a better way of doing it. Call the function once. It will call itself every 3 seconds Sub CalcAverage() StartRow = 12 'loop forever 'Get Last row LastRow = Range("A" & Rows.Count).End(xlUp).Row 'only collect data when 30 rows are reached If (LastRow - StartRow) + 1 = 30 Then 'perform averages on last 30 rows FirstRow = LastRow - 30 + 1 For ColCount = 1 To Range("AZ10").Column Set AverageRange = _ Range(Cells(FirstRow, ColCount), _ Cells(LastRow, ColCount)) ColAverage = WorksheetFunction.Average(AverageRange) Cells(10, ColCount) = ColAverage Next ColCount End If Application.OnTime Now + _ TimeValue("00:00:03"), "CalcAverage" End Sub "soccerdav2003" wrote: Hey Joel, This isn't working. It's getting hung up on ColAverage. All I did was copy your code into my procedure form. "Joel" wrote: Try this code. Don't know what answer you want in A12? I put the average of the last 30 entries in row 10 for each column. Sub CalcAverage() StartRow = 12 'loop forever Do While (1) 'Get Last row LastRow = Range("A" & Rows.Count).End(xlUp).Row 'only collect data when 30 rows are reached If (LastRow - StartRow) + 1 = 30 Then 'perform averages on last 30 rows FirstRow = LastRow - 30 + 1 For ColCount = 1 To Range("AZ10").Column Set AverageRange = _ Range(Cells(FirstRow, ColCount), Cells(LastRow, ColCount)) ColAverage = WorksheetFunction.Average(AverageRange) Cells(10, ColCount) = ColAverage Next ColCount End If Application.Wait Now() + Second(10) Loop End Sub "soccerdav2003" wrote: Hi, I've been attempting to write a macro to do this and received prior feedback. First response was I did not need a macro, I can use the formula: =IF(COUNT(A10:A1010),AVERAGE(A1010:INDEX(A10:A1010 ,INDEX(LARGE((A10:A1010<"")*ROW(A10:A1010),31),0) )),0) Even though this works, I can't make heads or tails of this. When I try to break this down into smaller functions, it falls apart What is going on by multiplying ((A10:A1010<")*Row(A10:1010),31)0) Using Nested indexes? What does A1010<"" mean? What are some rules for cell references? How do I make a cell reference a variable? Can somone please dicipher this... for me? Also, what if the number of data points collected has not reached 30 yet? Will this function fail? It does not appear to be failing when I test it with just 3 values, but I dont' really understand specifically what the function is doing. __________________________________________________ __________________ The second response was a macro. Sub Math() Dim lastRow As Long Dim sh As Worksheet For Each sh In Worksheets sh.Activate With sh lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Cells(lastRow, "B").FormulaR1C1 _ = "=average(r[-1]c:r[-30]c)" End With Next sh End Sub __________________________________________________ __________________ There are a few things that I would like it to do differently. I want the macro to (starting from Row 12) find the last row and then average the last 30 entities, and post the result on Row 10. I only need to run the macro on a specific worksheet, not multiple sheets. I want to start the macro running once I get 30 sets of data Also, data is recorded in column's A:AZ, and I have to perform this function on every column throughout the entire spectrum. Every so often, I hit a button to copy and paste special the values to another spreadsheet, so this macro has to continue to run while I run another macro at random intervals. I would like to increase my data field from 1200 points to 12,000 points so I can run my test for anywhere from 1 hour (data collected every 3 seconds) to 10 hours __________________________________________________ __________________ Also, in the statement "=average(r[-1]c:r[-30]c)", is there a way to replace the -1 and -30 with counters to represent the row index number like "x" and "y", where "X"=50 and "y" =80. How do I get the macro to put the answer in a specific Row that I activate versus .Cells(lastRow,"A"). I want the answer to be displayed on Cells("A12") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching multiple data to show desired result | Excel Discussion (Misc queries) | |||
how do I write a macro to show the find dialog box in excel 2003 | Excel Discussion (Misc queries) | |||
Advanced formula - Return result & Show Cell Reference of result | Excel Worksheet Functions | |||
How identify data of two coulams,Every cell count and show result | Excel Discussion (Misc queries) | |||
I need to find a macro to find data cut and paste to another colu. | Excel Programming |