Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to go to last row with values and average previous 30 values
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to go to last row with values and average previous 30 values
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,I NDEX(LARGE((B2:B1000<"")*ROW(B2:B1000),31),0))),0 ) Mike "soccerdav2003" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to go to last row with values and average previous 30 va
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--- -- RyGuy "Mike H" wrote: 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,I NDEX(LARGE((B2:B1000<"")*ROW(B2:B1000),31),0))),0 ) Mike "soccerdav2003" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to go to last row with values and average previous 30 va
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 "Mike H" wrote: 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,I NDEX(LARGE((B2:B1000<"")*ROW(B2:B1000),31),0))),0 ) Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TO Average Previous values | Excel Worksheet Functions | |||
Average of previous values | Excel Discussion (Misc queries) | |||
average values in non-contiguous cells, ignoring 0 values | Excel Worksheet Functions | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
collecting previous values | Excel Programming |