+ Reply to Thread
Results 1 to 11 of 11

Using Max function in mixed cell

  1. #1
    Registered User
    Join Date
    07-23-2004
    MS-Off Ver
    2007
    Posts
    94

    Using Max function in mixed cell

    Hello,

    Im having trouble using MAX in a mixed cell.

    My data in column B for example is:

    201301 January
    201302 February
    etc

    Using =Max(B:B) however returns a 0 value.

    Can anyone help?

    Many thanks

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Using Max function in mixed cell

    oNE SIMPLE WAY IS USING A HELPER AND HIDDEN COLUMN. lET'S SAY COLUMN c.

    In C1 and copy down use this.

    =LEFT(B1,6)+0

    Then use (MAX(C:C)
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    07-23-2004
    MS-Off Ver
    2007
    Posts
    94

    Re: Using Max function in mixed cell

    Thanks - Ive tried to combime into a single formula but it #Value's out.
    Is there no way to do it without adding a new column and increasing the amount of data in the spreadsheet?

  4. #4
    Forum Contributor BenMiller's Avatar
    Join Date
    12-06-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Using Max function in mixed cell

    =MAX(--LEFT(B:B,6)) confirmed with CTRL+SHIFT+ENTER (array formula)
    "I'm not a perfectionist; I'm just perfect."

  5. #5
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Using Max function in mixed cell

    ARRAY formula

    =MAX(--LEFT(B:B,6))

    But i insist that with helper column is better.

  6. #6
    Registered User
    Join Date
    07-23-2004
    MS-Off Ver
    2007
    Posts
    94

    Re: Using Max function in mixed cell

    Thanks for the suggestion - the array formula returns a 0 value

  7. #7
    Forum Contributor BenMiller's Avatar
    Join Date
    12-06-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Using Max function in mixed cell

    Quote Originally Posted by LB79 View Post
    Thanks for the suggestion - the array formula returns a 0 value
    Change B:B to your actual data range. Assuming there are no blank cells.

  8. #8
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Using Max function in mixed cell

    ................................
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-23-2004
    MS-Off Ver
    2007
    Posts
    94

    Re: Using Max function in mixed cell

    Thanks Ben - Seems to have resolved it and now works perfectly!

    Fotis - Thanks also for your suggestion - I think adding an additional column of data should be avoided where possible.

  10. #10
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Using Max function in mixed cell

    Quote Originally Posted by LB79 View Post

    Fotis - Thanks also for your suggestion - I think adding an additional column of data should be avoided where possible.
    You are welcome. What you say is the one side of the coin. The other side, says that it's better to keep the formulas as much simple as you can, specially if you handle big files. And helper columns help us to keep our data simple and not heavy!

  11. #11
    Forum Contributor BenMiller's Avatar
    Join Date
    12-06-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Using Max function in mixed cell

    Quote Originally Posted by LB79 View Post
    Thanks Ben - Seems to have resolved it and now works perfectly!
    No problem ... glad to help Appreciate the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1