Max function

  • Thread starter Thread starter lee taylor
  • Start date Start date
L

lee taylor

I have a column of numbers. some of which are postive, some are negative. I
want to find the maximum number i.e If there are 2 numbers, 100 and -200, i
want it to return the value of 200. I have tried the MAX function but this
returns a value of 100 in this example.

thanks
 
Try this ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of just
Enter):

=MAX(ABS(A1:A10))

Note: You can avoid the C+S+E with this version:
=MAX(INDEX(ABS(A1:A10),0))

Change range references to suit your situation.

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
Sort of, my values are not a range though. I want to find the maximum from
cells A1, A5 and A10.

thanks
 
In that case, and with those few values, try this ARRAY FORMULA (committed
with Ctrl+Shift+Enter, instead of just Enter):
=MAX(ABS(A1),ABS(A5),ABS(A10))

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
yes, thats nearly there. but i forgot, if the maximum value is a negative
value, can i return it as a negative? i.e. If the two values are 100
and -200 i want the function to return the maximum value of -200.

thanks for your help
 
Ron,

I don't think that your formula need to be array entered does it?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Hi, Sandy
I don't think that your formula need to be array entered does it?
Click to expand...

In Excel 2003, I need to C+S+E the formula. However, without C+S+E the
formula simply returns the value of the first cell in the list.
--------------------------

Best Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
Ron Coderre said:
In Excel 2003, I need to C+S+E the formula. However, without C+S+E the
formula simply returns the value of the first cell in the list.
Click to expand...

Well that's progress for you! In XL97 it works just fine normally entered.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Hi, Dave

Thanks for making sure I don't have a hardware problem
(a loose nut in front of my keyboard!), but here's my situation:

Cells A1:A10 contain

100
(blank)
(blank)
(blank)
-500
(blank)
(blank)
(blank)
(blank)
200

B1: =MAX(ABS(A1:A10))
The returned value is: 100

Put 100 in A2 and erase A1....
the returned value is: 0

C+S+E the formula....B1 returns: 500

I'm using Excel 2003 SP2. Are you experiencing something different?

Best Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
Ron,

I believe Dave's post was misposted and should have been directed at Sandy?
Otherwise he's out cycling on this one


--


Regards,


Peo Sjoblom



Ron Coderre said:
Hi, Dave

Thanks for making sure I don't have a hardware problem
(a loose nut in front of my keyboard!), but here's my situation:

Cells A1:A10 contain

100
(blank)
(blank)
(blank)
-500
(blank)
(blank)
(blank)
(blank)
200

B1: =MAX(ABS(A1:A10))
The returned value is: 100

Put 100 in A2 and erase A1....
the returned value is: 0

C+S+E the formula....B1 returns: 500

I'm using Excel 2003 SP2. Are you experiencing something different?

Best Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
Click to expand...
 
Hi Ron & Dave,

Here's my situation:

Brand new work book, (just in case),

A1: 200
A5: -500
A10: 200

Formula:
=MAX(ABS(A1),ABS(A5),ABS(A10))
(normally entered)

Returns 500

Why have you suddenly started testing =MAX(ABS(A1:A10))?

When I try that formula I on the above data I get 0 returned normally
entered and 500 array entered.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Ron Coderre said:
Hi, Dave

Thanks for making sure I don't have a hardware problem
(a loose nut in front of my keyboard!), but here's my situation:

Cells A1:A10 contain

100
(blank)
(blank)
(blank)
-500
(blank)
(blank)
(blank)
(blank)
200

B1: =MAX(ABS(A1:A10))
The returned value is: 100

Put 100 in A2 and erase A1....
the returned value is: 0

C+S+E the formula....B1 returns: 500

I'm using Excel 2003 SP2. Are you experiencing something different?

Best Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
Click to expand...
 
Interesting, I thought your remark (and Dave's) were directed at the
MAX(ABS(Range)) post by Ron which obviously needs to be array entered but
you were directing it at Ron's post about non adjacent cells and there of
course you are correct

Sorry for misunderstanding


--


Regards,


Peo Sjoblom



Sandy Mann said:
Hi Ron & Dave,

Here's my situation:

Brand new work book, (just in case),

A1: 200
A5: -500
A10: 200

Formula:
=MAX(ABS(A1),ABS(A5),ABS(A10))
(normally entered)

Returns 500

Why have you suddenly started testing =MAX(ABS(A1:A10))?

When I try that formula I on the above data I get 0 returned normally
entered and 500 array entered.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
Hi, Sandy

Regarding:
You're right....=MAX(ABS(A1),ABS(A5),ABS(A10)) does NOT need C+S+E.

Evidently, I DO have that "hardware problem" I mentioned to Dave? :\
Time to see if the Boston Home for the Befuddled has an opening for me.

Best Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


Sandy Mann said:
Hi Ron & Dave,

Here's my situation:

Brand new work book, (just in case),

A1: 200
A5: -500
A10: 200

Formula:
=MAX(ABS(A1),ABS(A5),ABS(A10))
(normally entered)

Returns 500

Why have you suddenly started testing =MAX(ABS(A1:A10))?

When I try that formula I on the above data I get 0 returned normally
entered and 500 array entered.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
Back
Top