 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2007Max function
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) >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 Sort of, my values are not a range though. I want to find the maximum from cells A1, A5 and A10. thanks > Try this ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of just > Enter): [quoted text clipped - 20 lines] >> >> 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) > Sort of, my values are not a range though. I want to find the maximum from > cells A1, A5 and A10. [quoted text clipped - 25 lines] >>> >>> thanks 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 > In that case, and with those few values, try this ARRAY FORMULA (committed > with Ctrl+Shift+Enter, instead of just Enter): [quoted text clipped - 37 lines] >>>> >>>> thanks =IF(MAX(A1,A5,A10)>MAX(-A1,-A5,-A10),MAX(A1,A5,A10),-MAX(-A1,-A5,- A10)) Regards, Bernd thank you > =IF(MAX(A1,A5,A10)>MAX(-A1,-A5,-A10),MAX(A1,A5,A10),-MAX(-A1,-A5,- > A10)) > > Regards, > Bernd Ron, I don't think that your formula need to be array entered does it?  SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > In that case, and with those few values, try this ARRAY FORMULA (committed > with Ctrl+Shift+Enter, instead of just Enter): [quoted text clipped - 37 lines] >>>> >>>> thanks Hi, Sandy > I don't think that your formula need to be array entered does it? 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, > [quoted text clipped - 41 lines] >>>>> >>>>> thanks > 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. Well that's progress for you! In XL97 it works just fine normally entered. SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Hi, Sandy >> I don't think that your formula need to be array entered does it? [quoted text clipped - 54 lines] >>>>>> >>>>>> thanks You sure that your test data just didn't have the max(abs()) in A1? > Hi, Sandy > > I don't think that your formula need to be array entered does it? [quoted text clipped - 63 lines] > >>>>> > >>>>> thanks SignatureDave Peterson 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) > You sure that your test data just didn't have the max(abs()) in A1? > [quoted text clipped - 66 lines] >> >>>>> >> >>>>> thanks Ron, I believe Dave's post was misposted and should have been directed at Sandy? Otherwise he's out cycling on this one SignatureRegards, Peo Sjoblom > Hi, Dave > [quoted text clipped - 101 lines] >>> >>>>> >>> >>>>> thanks 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. SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Hi, Dave > [quoted text clipped - 101 lines] >>> >>>>> >>> >>>>> thanks 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 SignatureRegards, Peo Sjoblom > Hi Ron & Dave, > [quoted text clipped - 123 lines] >>>> >>>>> >>>> >>>>> thanks Hi, Sandy Regarding: >>Why have you suddenly started testing =MAX(ABS(A1:A10))? 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) > Hi Ron & Dave, > [quoted text clipped - 123 lines] >>>> >>>>> >>>> >>>>> thanks I'm just happy that I never make these kinds of misteaks! <vbg> > Hi, Sandy > [quoted text clipped - 152 lines] > >>> > >>> Dave Peterson SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.