end of column

[inquirer]

I have a series of worksheets and want to perform various statistics on data
in columns such as =average(b2:b1516).
But instead of putting in the last cell b1516 I would like to put in
something like endo of data in col b (assuming that there are no gaps in the
range b2:b1516)
Then when I copy the formula to another column I would not have to input the
exact last cell address.
Can this be done without writing a macro or vba?
Thanks
Chris

 

[Aladin Akyurek]

A1:

=MATCH(9.99999999999999E+307,B:B)

which allows you rewrite

=AVERAGE(B2:B1516)

as:

=AVERAGE(B2:INDEX(B:B,$A$1))

 

[Don Guillett]

if
=average(d:d)
doesn't do it, try
=AVERAGE(INDIRECT("D1:d"&MATCH(9.999999E+307,D)))

 

[Don Guillett]

To make it work for ANY column, place this UDF into a regular module (not a
sheet module)

Function cl(x) As String
cl = Left(Cells(1, x).Address(False, False), 1 - (x > 26))
End Function

Then use this formula which may be copied across columns starting with col D
=AVERAGE(INDIRECT(cl(COLUMN(D1))&"1:"&cl(COLUMN(D1))&MATCH(9.999999E+307,D
)))

 

[Dave Peterson]

You can even use R1C1 reference style within the =indirect() function:

=AVERAGE(INDIRECT("r2c"&COLUMN(D1)&
":r"&MATCH(9.999999E+307,D)&"c"&COLUMN(D1),FALSE))

(I changed it to start at Row 2, too. Tutu??)

But maybe adjusting Don's suggestion (=average(d:d)) to avoid whatever's in D1
would work ok:

=(SUM(D)-SUM(D1))/(COUNT(D)-COUNT(D1))

 

[inquirer]

Many thanks for your suggestions

You can even use R1C1 reference style within the =indirect() function:

=AVERAGE(INDIRECT("r2c"&COLUMN(D1)&
":r"&MATCH(9.999999E+307,D)&"c"&COLUMN(D1),FALSE))

(I changed it to start at Row 2, too. Tutu??)

But maybe adjusting Don's suggestion (=average(d:d)) to avoid whatever's in D1
would work ok:

=(SUM(D)-SUM(D1))/(COUNT(D)-COUNT(D1))

 

[Soo Cheon Jheong]

Hi,


=AVERAGE(B2:INDEX(B:B,MATCH(MAX(B:B)+1,B:B)))


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--