How to find maximum number from range of positive & negative number?

  • Thread starter Thread starter Wee Meng
  • Start date Start date
W

Wee Meng

Hi,

Could any kind soul help me on how to determine the
maximum number including negative numbers from range of
cell. For Eg,

A B C D E
1 1 2 -4 -10 5


=> The answer for this should be -10. What formula
should I use for the above to find the maximum? I need an
answer of -10 for this example.

Appreciate you help.

Thanks
Wee Meng
 
Hello,

Try this : =MAX(ABS(Range))) "Range" is the numbers under consideration (
or whatever name you choose to give it). This is Array enterd
(CTRL+SHIFT+ENTER).
 
After naming the cells A1 to E1 as, lets
say, "YourRange", you can enter formula
=INDEX(YourRange;MATCH(MAX(ABS(YourRange));ABS
(YourRange);0)) in the cell you want your result. Don't
forget to use Ctrl+Shift+Enter to enter the formula
because it contains the Array "YourRange".

E.G.
 
-----Original Message-----
Hello,

Try this : =MAX(ABS(Range))) "Range" is the numbers under consideration (
or whatever name you choose to give it). This is Array enterd
(CTRL+SHIFT+ENTER).
Click to expand...


Yes I have tried Max(ABS(Range)) follow
by "CTRL+SHIFT+ENTER" and it returns 10. But I need it to
return -10.

Can anyone help on this.

Regards
Wee Meng
 
-----Original Message-----
Hi,

Could any kind soul help me on how to determine the
maximum number including negative numbers from range of
cell. For Eg,

A B C D E
1 1 2 -4 -10 5


=> The answer for this should be -10. What formula
should I use for the above to find the maximum? I need an
answer of -10 for this example. I've tried Max(ABS
Click to expand...
(Range)) followed by "Control+Shift+Enter" and it returns
10 without the "-" sign.
 
Hi Gioka,

I tried your formula, but the excel returns error saying
the formula you typed contains an error. Pls help.
Thanks.

Regards
Wee Meng
 
Indeed, if the formula included the negative sign in the answer, then the
answer would be erroneous. (-10) is the minimum, NOT the maximum. You are
asking for a contradiction here. The previous array formula gives you the
correct answer; it simply ignores signs when finding the value.
 
Hi Gioka,

I have got it, instead of ; it should be , in the formula
and it works.

Thanks a million. Have a nice day!

Cheers
Wee Meng
 
Hi Gioka,

Another problem, If I expand your formula to compute
multiple rows. I got a result of #NA. See eg below, I
need Excel to return -200.Can you help on this?

A B C D E
1 100 -200 -10 -20 40
2 300 -100 50 80 60


Regards
Wee Meng
 
In order to do this you have to name the whole array lets
say "Range". Then you have to change the formula to =INDEX
(INDEX(Range;1;0);MATCH(MAX(ABS(INDEX(Range;1;0)));ABS
(INDEX(Range;1;0));0))(Don't forget to change the
semicolons...). Then you cand expand this to the number
of the rows your array has.
You may notice that you have to manually change the
number "1" (the second parameter of the INDEX function to
2,3,....
If you want it to change automatically, you have to
create one column containing the Row number, let's say
column F. Then you can change the number "1" to F1 and
let it change automatically aas you expand the formula...

Hope that I solve your problem

E.G.
 
Why not just

=INDEX(Range,MAX((ABS(Range)=MAX(ABS(Range)))*(ROW(Range)))-(CELL("row",Rang
e)-1),MAX((ABS(Range)=MAX(ABS(Range)))*(COLUMN(Range)))-(CELL("col",Range)-1
))

Entered with ctrl + shift & enter

no need for help columns etc, if the range is named Range the above will
return max absolute value

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Hi Gioka,

Another problem, If I expand your formula to compute
multiple rows. I got a result of #NA. See eg below, I
need Excel to return -200.Can you help on this?

A B C D E
1 100 -200 -10 -20 40
2 300 -100 50 80 60


Regards
Wee Meng
Click to expand...

The array-entered formula:

=SUM((ABS(rng)=MAX(ABS(rng)))*rng)

will return the max absolute number in any range.

But in your example, wouldn't you want 300 returned, or are you just checking
one row at a time?

If the latter, then substitute A1:E1 for rng and copy/drag the formula down.


--ron
 
Hi Ron,

Its works with your simple formula.
Thanks a lot and have a nice day!

Cheers
Wee Meng
 
Back
Top