Macro to find last row with Data, avg last 30, show result on row

[soccerdav2003]

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")

 

[Joel]

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

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")

 

[soccerdav2003]

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?

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

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")

 

[Joel]

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))

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?

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

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")

 

[soccerdav2003]

Hey Joel,
This isn't working. It's getting hung up on ColAverage. All I did was copy
your code into my procedure form.

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

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")

 

[Joel]

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

Hey Joel,
This isn't working. It's getting hung up on ColAverage. All I did was copy
your code into my procedure form.

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

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")

 

[soccerdav2003]

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

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

Hey Joel,
This isn't working. It's getting hung up on ColAverage. All I did was copy
your code into my procedure form.

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

:

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")

 

[Joel]

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

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

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

Hey Joel,
This isn't working. It's getting hung up on ColAverage. All I did was copy
your code into my procedure form.

:

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

:

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")