Max function
 

I have two different series in one column i.e one starts with 10000 and
other with 20000 , I need to find the max number for each one . What should
I do limit the range to desire series?

Max function
 

Hi,

Try these ARRAY formula. see below on how to enter them. Adjudt the 1000 to
be larger than your expected range

For the lower range
=MAX(IF(A1:A1000<20000,A1:A1000))

for the upper range
=MAX(IF(A1:A100020000,A1:A1000))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Khusro" wrote:

I have two different series in one column i.e one starts with 10000 and
other with 20000 , I need to find the max number for each one . What should
I do limit the range to desire series?

Max function
 

On reflection the second formula should be

=MAX(IF(A1:A1000=20000,A1:A1000))

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

Try these ARRAY formula. see below on how to enter them. Adjudt the 1000 to
be larger than your expected range

For the lower range
=MAX(IF(A1:A1000<20000,A1:A1000))

for the upper range
=MAX(IF(A1:A100020000,A1:A1000))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Khusro" wrote:

I have two different series in one column i.e one starts with 10000 and
other with 20000 , I need to find the max number for each one . What should
I do limit the range to desire series?

Max function
 

Hi,

You may also try the DMAX function. Suppose the column heading is Numbers
in cell C3. Suppose data is in range C4:C11

In a C14:E14, type Numbers and in C15, type =20000. In D15, type <=29999.
In cell E15, type =DMAX(C3:C11,E14,C14:D15)
In a C17:E17, type Numbers and in C18, type <=20000. In cell E18, type
=DMAX(C3:C11,E17,C17:D18)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Khusro" wrote in message
...
I have two different series in one column i.e one starts with 10000 and
other with 20000 , I need to find the max number for each one . What
should
I do limit the range to desire series?