Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that counts rows and averages data in row
I have a Excel file that is imported in from another program as a CSV import.
What I want to do is create a macro that does many different things to format the sheet to what we need. I can accomplish all, except the following: I want to be able to have a line at the bottom that has a formula such as =SUM(First row of data:Last Row of Data)/The number of rows of data (The total number of rows could be anywhere from 200 to 2000.) A small sample would be the following: A B C D 1 Item1 $0.50 EACH 3.0 2 Item2 $1.50 CASE 1.5 3 Item3 $0.75 EACH 4.5 4 Item4 $2.00 EACH 0.75 Average of D: =SUM(D1:D4)/4 Thanks for any help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that counts rows and averages data in row
try this
Option Explicit Sub avgCol() Dim lastrow As Long lastrow = Worksheets("Sheet1").Cells(Rows.Count, "D").End(xlUp).Row Range("D" & lastrow + 1).Formula = "=average(D1:D" & lastrow & ")" End Sub -- Gary "KnightRiderAW" wrote in message ... I have a Excel file that is imported in from another program as a CSV import. What I want to do is create a macro that does many different things to format the sheet to what we need. I can accomplish all, except the following: I want to be able to have a line at the bottom that has a formula such as =SUM(First row of data:Last Row of Data)/The number of rows of data (The total number of rows could be anywhere from 200 to 2000.) A small sample would be the following: A B C D 1 Item1 $0.50 EACH 3.0 2 Item2 $1.50 CASE 1.5 3 Item3 $0.75 EACH 4.5 4 Item4 $2.00 EACH 0.75 Average of D: =SUM(D1:D4)/4 Thanks for any help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that counts rows and averages data in row
Range("D1").end(xldown)(2).FormulaR1C1 = "=Average(R1C:R[-1]C)"
-- Regards, Tom Ogilvy "KnightRiderAW" wrote in message ... I have a Excel file that is imported in from another program as a CSV import. What I want to do is create a macro that does many different things to format the sheet to what we need. I can accomplish all, except the following: I want to be able to have a line at the bottom that has a formula such as =SUM(First row of data:Last Row of Data)/The number of rows of data (The total number of rows could be anywhere from 200 to 2000.) A small sample would be the following: A B C D 1 Item1 $0.50 EACH 3.0 2 Item2 $1.50 CASE 1.5 3 Item3 $0.75 EACH 4.5 4 Item4 $2.00 EACH 0.75 Average of D: =SUM(D1:D4)/4 Thanks for any help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that counts rows and averages data in row
Dim iLastRow as Long
iLastRow = Cells(Rows.Count,"D").End(xlUp).Row Cells(iLastRow + 1, "D").Formula = "=AVERAGE(D1:D" & iLastRow & ")" -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... I have a Excel file that is imported in from another program as a CSV import. What I want to do is create a macro that does many different things to format the sheet to what we need. I can accomplish all, except the following: I want to be able to have a line at the bottom that has a formula such as =SUM(First row of data:Last Row of Data)/The number of rows of data (The total number of rows could be anywhere from 200 to 2000.) A small sample would be the following: A B C D 1 Item1 $0.50 EACH 3.0 2 Item2 $1.50 CASE 1.5 3 Item3 $0.75 EACH 4.5 4 Item4 $2.00 EACH 0.75 Average of D: =SUM(D1:D4)/4 Thanks for any help! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that counts rows and averages data in row
With a little tweaking to fit into my program, this worked great! Thanks for
your help! "Gary Keramidas" wrote: try this Option Explicit Sub avgCol() Dim lastrow As Long lastrow = Worksheets("Sheet1").Cells(Rows.Count, "D").End(xlUp).Row Range("D" & lastrow + 1).Formula = "=average(D1:D" & lastrow & ")" End Sub -- Gary "KnightRiderAW" wrote in message ... I have a Excel file that is imported in from another program as a CSV import. What I want to do is create a macro that does many different things to format the sheet to what we need. I can accomplish all, except the following: I want to be able to have a line at the bottom that has a formula such as =SUM(First row of data:Last Row of Data)/The number of rows of data (The total number of rows could be anywhere from 200 to 2000.) A small sample would be the following: A B C D 1 Item1 $0.50 EACH 3.0 2 Item2 $1.50 CASE 1.5 3 Item3 $0.75 EACH 4.5 4 Item4 $2.00 EACH 0.75 Average of D: =SUM(D1:D4)/4 Thanks for any help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
average daily counts to weekly counts | Excel Discussion (Misc queries) | |||
Pulling Multiple Totals, Averages, and Counts | Excel Discussion (Misc queries) | |||
counts rows in data | Excel Programming | |||
Averages and Counts from different columns | Excel Worksheet Functions | |||
macro that counts lines with certain color | Excel Programming |