Hello all,

I'm attempting to construct a 'dynamic' formula to find the maximum value in
a column of figures that will, over time, be added to.

Can someone please help me with a formula to find Maximum value in Column F?
I've been playing with variations of the following, but without success
(obviously):

=Max(F1:F&Count(F:F))

Regards,

John

=MAX(F:F) will ignore blanks cells in column F and allows for blanks between
data.

But if you truly wanted a dynamic reference you could create a defined name.

InsertNameDefine

In the Refers to: dialog enter this formula

=OFFSET(Sheet1!$F$1,0,0,COUNTA(Sheet1!$F:$F),1)

Give it a name.........thename

In a cell enter =MAX(thename)

Note: the dynamic range must be contiguous


Gord Dibben MS Excel MVP



On Sun, 3 Feb 2008 10:43:00 +1100, "John Taylor" wrote:

Hello all,

I'm attempting to construct a 'dynamic' formula to find the maximum value in
a column of figures that will, over time, be added to.

Can someone please help me with a formula to find Maximum value in Column F?
I've been playing with variations of the following, but without success
(obviously):

=Max(F1:F&Count(F:F))

Regards,

John

Gord,

Thanks for the quick reply, and the information contained therein. Will
give it a go.

Regards,

John

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
=MAX(F:F) will ignore blanks cells in column F and allows for blanks
between
data.

But if you truly wanted a dynamic reference you could create a defined
name.

InsertNameDefine

In the Refers to: dialog enter this formula

=OFFSET(Sheet1!$F$1,0,0,COUNTA(Sheet1!$F:$F),1)

Give it a name.........thename

In a cell enter =MAX(thename)

Note: the dynamic range must be contiguous


Gord Dibben MS Excel MVP



On Sun, 3 Feb 2008 10:43:00 +1100, "John Taylor"
wrote:

Hello all,

I'm attempting to construct a 'dynamic' formula to find the maximum value
in
a column of figures that will, over time, be added to.

Can someone please help me with a formula to find Maximum value in Column
F?
I've been playing with variations of the following, but without success
(obviously):

=Max(F1:F&Count(F:F))

Regards,

John

Why not just use "=MAX(F:F)" ?


"John Taylor" wrote in message
...
Hello all,

I'm attempting to construct a 'dynamic' formula to find the maximum value
in a column of figures that will, over time, be added to.

Can someone please help me with a formula to find Maximum value in Column
F? I've been playing with variations of the following, but without success
(obviously):

=Max(F1:F&Count(F:F))

Regards,

John

G'day,

I simplified my original question. I'm actually using MAX in the following
array formula (where XJO is the range name I used, following Gord's
suggestion), and replacing XJO with simply F:F gives a #NUM! error:

=MAX(IF((Date_Range=J3)*(Date_Range<=J4),XJO,0))

Regards,

John

"DanRoss" wrote in message
...
Why not just use "=MAX(F:F)" ?


"John Taylor" wrote in message
...
Hello all,

I'm attempting to construct a 'dynamic' formula to find the maximum value
in a column of figures that will, over time, be added to.

Can someone please help me with a formula to find Maximum value in Column
F? I've been playing with variations of the following, but without
success (obviously):

=Max(F1:F&Count(F:F))

Regards,

John