end of column

I

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
 
A

Aladin Akyurek

A1:

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

which allows you rewrite

=AVERAGE(B2:B1516)

as:

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

Don Guillett

if
=average(d:d)
doesn't do it, try
=AVERAGE(INDIRECT("D1:d"&MATCH(9.999999E+307,D:D)))
 
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:D
)))
 
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: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:D)-SUM(D1))/(COUNT(D:D)-COUNT(D1))
 
I

inquirer

Many thanks for your suggestions

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

=AVERAGE(INDIRECT("r2c"&COLUMN(D1)&
":r"&MATCH(9.999999E+307,D: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:D)-SUM(D1))/(COUNT(D:D)-COUNT(D1))
Click to expand...
 
S

Soo Cheon Jheong

Hi,


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


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