Macro that counts rows and averages data in row

[KnightRiderAW]

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!

[Gary Keramidas]

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" <[email protected]> wrote in message
news:[email protected]...
>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!

[Tom Ogilvy]

Range("D1").end(xldown)(2).FormulaR1C1 = "=Average(R1C:R[-1]C)"

--
Regards,
Tom Ogilvy


"KnightRiderAW" <[email protected]> wrote in message
news:[email protected]...
> 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!

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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!

[KnightRiderAW]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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!
>
>
>