Good day everyone,

On An Excel sheet, I have a column filled with account numbers. In cell E1,
I have the following formula:
=LARGE($AC$2:$AC$65536,1)

Which gives me the highest number in the column. However, I 'd like to have
for result the highest number as long as this number is lower than 200,000

Would anyone know how to write the formula?

Thanks for your time

Denys

I smell an array formula. Try something like this, but *YOU MUST HOLD CTRL
WHEN PRESSING ENTER* to make it an array formula (shown with curly braces if
done right):

=IF($AC$2:$AC$65536200000,LARGE($AC$2:$AC$65536,1 ),0)


"Denys" wrote in message
...
Good day everyone,

On An Excel sheet, I have a column filled with account numbers. In cell
E1,
I have the following formula:
=LARGE($AC$2:$AC$65536,1)

Which gives me the highest number in the column. However, I 'd like to
have
for result the highest number as long as this number is lower than 200,000

Would anyone know how to write the formula?

Thanks for your time

Denys

Hi, JM,

Thanks for your answer, Unfortunately, the answer becomes 457000.

But you gave me an idea. I'll try and come back...

Thanks again

Denys

"JM" wrote:

I smell an array formula. Try something like this, but *YOU MUST HOLD CTRL
WHEN PRESSING ENTER* to make it an array formula (shown with curly braces if
done right):

=IF($AC$2:$AC$65536200000,LARGE($AC$2:$AC$65536,1 ),0)


"Denys" wrote in message
...
Good day everyone,

On An Excel sheet, I have a column filled with account numbers. In cell
E1,
I have the following formula:
=LARGE($AC$2:$AC$65536,1)

Which gives me the highest number in the column. However, I 'd like to
have
for result the highest number as long as this number is lower than 200,000

Would anyone know how to write the formula?

Thanks for your time

Denys

Oops, change the to <. Duh.


"Denys" wrote in message
...
Hi, JM,

Thanks for your answer, Unfortunately, the answer becomes 457000.

But you gave me an idea. I'll try and come back...

Thanks again

Denys

"JM" wrote:

I smell an array formula. Try something like this, but *YOU MUST HOLD
CTRL
WHEN PRESSING ENTER* to make it an array formula (shown with curly
braces if
done right):

=IF($AC$2:$AC$65536200000,LARGE($AC$2:$AC$65536,1 ),0)


"Denys" wrote in message
...
Good day everyone,

On An Excel sheet, I have a column filled with account numbers. In
cell
E1,
I have the following formula:
=LARGE($AC$2:$AC$65536,1)

Which gives me the highest number in the column. However, I 'd like to
have
for result the highest number as long as this number is lower than
200,000

Would anyone know how to write the formula?

Thanks for your time

Denys

you could use the filter and the subtotal command
select column ac
click data/filter/ auto filter
click the arrow
select custom, is less than or equal to and enter 200000
then use the subtotal function

=SUBTOTAL(4,AC2:AC65535)

--


Gary


"Denys" wrote in message
...
Good day everyone,

On An Excel sheet, I have a column filled with account numbers. In cell
E1,
I have the following formula:
=LARGE($AC$2:$AC$65536,1)

Which gives me the highest number in the column. However, I 'd like to
have
for result the highest number as long as this number is lower than 200,000

Would anyone know how to write the formula?

Thanks for your time

Denys

IF ? your numbers are sorted in ascending order try this:

=INDEX(ref,MATCH(200000,ref,1))

Regards,
Peter

"Denys" wrote in message
...
Good day everyone,

On An Excel sheet, I have a column filled with account numbers. In cell
E1,
I have the following formula:
=LARGE($AC$2:$AC$65536,1)

Which gives me the highest number in the column. However, I 'd like to
have
for result the highest number as long as this number is lower than 200,000

Would anyone know how to write the formula?

Thanks for your time

Denys

Thanks JM,

Have a nice day....

Denys

"JM" wrote:

Oops, change the to <. Duh.


"Denys" wrote in message
...
Hi, JM,

Thanks for your answer, Unfortunately, the answer becomes 457000.

But you gave me an idea. I'll try and come back...

Thanks again

Denys

"JM" wrote:

I smell an array formula. Try something like this, but *YOU MUST HOLD
CTRL
WHEN PRESSING ENTER* to make it an array formula (shown with curly
braces if
done right):

=IF($AC$2:$AC$65536200000,LARGE($AC$2:$AC$65536,1 ),0)


"Denys" wrote in message
...
Good day everyone,

On An Excel sheet, I have a column filled with account numbers. In
cell
E1,
I have the following formula:
=LARGE($AC$2:$AC$65536,1)

Which gives me the highest number in the column. However, I 'd like to
have
for result the highest number as long as this number is lower than
200,000

Would anyone know how to write the formula?

Thanks for your time

Denys