Hi all,

Is there a nice non-array formula (or a more efficient array formula) to
compute the largest value (positive or negative) in a range such as this
one:

-5, 2, 0, -2, 10, -11.

In this case the answer is -11.

I use this formula but I think there should be something better than that.

{=IF(MAX(range)=MAX(ABS(range)),MAX(range),-MAX(range)))}

Thanks for any tips.

Milos.

Miles,

Not non-array, but simpler

=MAX(ABS(A1:A100))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Milos Setek" wrote in message
...
Hi all,

Is there a nice non-array formula (or a more efficient array formula) to
compute the largest value (positive or negative) in a range such as this
one:

-5, 2, 0, -2, 10, -11.

In this case the answer is -11.

I use this formula but I think there should be something better than that.

{=IF(MAX(range)=MAX(ABS(range)),MAX(range),-MAX(range)))}

Thanks for any tips.

Milos.

Hi Bob
this was my first idea also, but this will return 11 for his example
data but the OP wants -11.
So I'm still trying to create a simpler formula - but did not
succeed....

Best regards
Frank


Bob Phillips wrote:
Miles,

Not non-array, but simpler

=MAX(ABS(A1:A100))


"Milos Setek" wrote in message
...
Hi all,

Is there a nice non-array formula (or a more efficient array
formula) to compute the largest value (positive or negative) in a
range such as this one:

-5, 2, 0, -2, 10, -11.

In this case the answer is -11.

I use this formula but I think there should be something better than
that.

{=IF(MAX(range)=MAX(ABS(range)),MAX(range),-MAX(range)))}

Thanks for any tips.

Milos.

Thank you Bob, but I want to return the negative sign, so in the example I
gave the number returned must be -11. If -11 was not there then the largest
would be 10.

Milos.

"Bob Phillips" wrote in message
...
Miles,

Not non-array, but simpler

=MAX(ABS(A1:A100))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Milos Setek" wrote in message
...
Hi all,

Is there a nice non-array formula (or a more efficient array formula) to
compute the largest value (positive or negative) in a range such as this
one:

-5, 2, 0, -2, 10, -11.

In this case the answer is -11.

I use this formula but I think there should be something better than
that.

{=IF(MAX(range)=MAX(ABS(range)),MAX(range),-MAX(range)))}

Thanks for any tips.

Milos.

Hi Frank,

Missed that subtle nuance. INDEX(MATCH?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi Bob
this was my first idea also, but this will return 11 for his example
data but the OP wants -11.
So I'm still trying to create a simpler formula - but did not
succeed....

Best regards
Frank


Bob Phillips wrote:
Miles,

Not non-array, but simpler

=MAX(ABS(A1:A100))


"Milos Setek" wrote in message
...
Hi all,

Is there a nice non-array formula (or a more efficient array
formula) to compute the largest value (positive or negative) in a
range such as this one:

-5, 2, 0, -2, 10, -11.

In this case the answer is -11.

I use this formula but I think there should be something better than
that.

{=IF(MAX(range)=MAX(ABS(range)),MAX(range),-MAX(range)))}

Thanks for any tips.

Milos.

Bob Phillips wrote:
Hi Frank,

Missed that subtle nuance. INDEX(MATCH?


Yeah
I tried that but the result is even more complicated than the OP's
solution. I only got to this non array solution:
=SUMPRODUCT((ABS(range)=MAX(ABS(range)))*(range))
not simpler but a non array one :-)

Frank

Hello again Bob and Frank,

The below formula is an array one, since I had to press ctrl+shift+enter to
get it working. However, it is clever.

The reason why I am asking this is because mine one gives me a #VALUE! error
when run from within a VBA macro, but manually it works so I am not sure
what is going on, and hence I wanted to try something either simpler or
non-array based.

Thank you all for your efforts.

Milos

"Frank Kabel" wrote in message
...
Bob Phillips wrote:
Hi Frank,

Missed that subtle nuance. INDEX(MATCH?


Yeah
I tried that but the result is even more complicated than the OP's
solution. I only got to this non array solution:
=SUMPRODUCT((ABS(range)=MAX(ABS(range)))*(range))
not simpler but a non array one :-)

Frank

Hi Milos
you're right. It is array entered. Missed that due to my small number
of testing data
Frank

Milos Setek wrote:
Hello again Bob and Frank,

The below formula is an array one, since I had to press
ctrl+shift+enter to get it working. However, it is clever.

The reason why I am asking this is because mine one gives me a
#VALUE! error when run from within a VBA macro, but manually it works
so I am not sure what is going on, and hence I wanted to try
something either simpler or non-array based.

Thank you all for your efforts.

Milos

"Frank Kabel" wrote in message
...
Bob Phillips wrote:
Hi Frank,

Missed that subtle nuance. INDEX(MATCH?


Yeah
I tried that but the result is even more complicated than the OP's
solution. I only got to this non array solution:
=SUMPRODUCT((ABS(range)=MAX(ABS(range)))*(range))
not simpler but a non array one :-)

Frank

And the winner is Mayur. Thank you lots for that and it is non-array!



"Mayur" wrote in message
...
=IF(ABS(MAX(range))<ABS(MIN(range)),MIN(range),MAX (range))

Sumproduct is an array formula anyway, even if you don't have to array enter
it.

--
Regards,
Tom Ogilvy

Frank Kabel wrote in message
...
Hi Milos
you're right. It is array entered. Missed that due to my small number
of testing data
Frank

Milos Setek wrote:
Hello again Bob and Frank,

The below formula is an array one, since I had to press
ctrl+shift+enter to get it working. However, it is clever.

The reason why I am asking this is because mine one gives me a
#VALUE! error when run from within a VBA macro, but manually it works
so I am not sure what is going on, and hence I wanted to try
something either simpler or non-array based.

Thank you all for your efforts.

Milos

"Frank Kabel" wrote in message
...
Bob Phillips wrote:
Hi Frank,

Missed that subtle nuance. INDEX(MATCH?


Yeah
I tried that but the result is even more complicated than the OP's
solution. I only got to this non array solution:
=SUMPRODUCT((ABS(range)=MAX(ABS(range)))*(range))
not simpler but a non array one :-)

Frank