Largest value in a range

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.
 
B

Bob Phillips

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)
 
F

Frank Kabel

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
 
M

Milos Setek

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.
 
B

Bob Phillips

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)
 
F

Frank Kabel

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
 
M

Milos Setek

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
 
F

Frank Kabel

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

Tom Ogilvy

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