Macro to go to last row with values and average previous 30 values

[soccerdav2003]

Hi Guru's
I'm new (clueless) writing macros in VB.

I have an application where I'm reading data from an Agilent DataLogger
using a program called intuit (complimentary excel add-in from Agilent)

As data records, it enters into a row and indexes and continues on until it
either times our or I kill it.

I would like to be able to write a program that will allow me to
automatically read the average of the last 30 values continuously and read
them in the first Row of my spreadsheet, since the only way I can currently
do this is stop the program, go to the last 30 sets of data, and calculate
the average.

When I currently do this, I have to stop the data logger and then restart
(pause doesn't work)

When I hit go, the data logger starts on line 1, which means I loose all the
previous history.

In using VB, I'm trying to use the formula command to average inside the
macro, but when I run it, it seems to be getting confused.

I'm also not exactly sure if I should use a do while loop or a counter or
what.
Any ideas would be extremely helpful.

 

[Mike H]

hi,

I may have misunderstood but you may not need a macro. Put this in b1
and it will average the last 30 values in that column and update as values
are added to the end of the column. You can adjust the 1000 to suit your
needs. Drag right for other columns

=IF(COUNT(B2:B1000),AVERAGE(B1000:INDEX(B2:B1000,INDEX(LARGE((B2:B1000<>"")*ROW(B2:B1000),31),0))),0)

Mike

 

[ryguy7272]

I think this will do what you want:
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


Regards,
Ryan---

 

[Bassman62]

Mike,

Your formula works wonderfully. I'm picking it apart to gain a better
understand of the functions used and am perplexed by some portions.
If I enter into a cell ' =LARGE((B2:B1000<>"")*ROW(B2:B1000),31) ', the
result is #NUM error.
How does ' (B2:B1000<>"")*ROW(B2:B1000) ' evaluate to an array for the
"Large" function? and Why does it work within the larger formula by not in a
cell by itself?
Thank you.

Dave

 

[soccerdav2003]

Hi Mike H.
Your formula does work, however, I'm perplexed by the indexing and using the
combination of Large and Row product.

Could you explain this in more detail?
I appreciate it.