Largest value in a range

  • Thread starter Thread starter Milos Setek
  • Start date Start date
M

Milos Setek

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)
 
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
 
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.
 
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)
 
Bob said:
Hi Frank,

Missed that subtle nuance. INDEX(MATCH?
Click to expand...
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
 
Hi Milos
you're right. It is array entered. Missed that due to my small number
of testing data
Frank
 
Back
Top