+ Reply to Thread
Results 1 to 13 of 13

MAX function if part of number is constant

  1. #1
    Registered User
    Join Date
    11-10-2020
    Location
    Worthing
    MS-Off Ver
    2016
    Posts
    8

    MAX function if part of number is constant

    Hello

    How do i use the MAX function if part of the number is a constant? eg

    21/001
    21/002
    21/003 etc

    I've been trying MAX(X:X) with no luck, any help appreciated.

    Nick

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: MAX function if part of number is constant

    Try this:

    =MAX(IF(ISTEXT($A$1:$A$3),--RIGHT($A$1:$A$3,3)))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    11-10-2020
    Location
    Worthing
    MS-Off Ver
    2016
    Posts
    8

    Re: MAX function if part of number is constant

    Thanks for the reply, this seems to bring back a value of 0.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: MAX function if part of number is constant

    You may need to enter it with CTRL+SHIFT+ENTER, not just ENTER. Your forum profile needs updating - 10 is not a version of Excel!

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    1
    21/001
    3
    2
    21/002
    3
    21/003
    Sheet: Sheet1

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: MAX function if part of number is constant

    If you don't understand my last post, please see the attachment for clarification.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-10-2020
    Location
    Worthing
    MS-Off Ver
    2016
    Posts
    8

    Re: MAX function if part of number is constant

    Hello

    Thanks again, I don't seem to be able to get it to work as i would like it.

    The number 21/??? will be added to throughout the year and i need the most recent number to always be recorded, the number could potentially get to around 21/2000.

    I'm working on excel 2016.

    Thanks again.

    Nick

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: MAX function if part of number is constant

    PLEASE UPDATE YOUR PROFILE as requested.

    Try this;

    =MAX(IF(ISTEXT($A$1:$A$4),--MID($A$1:$A$4,FIND("/",$A$1:$A$4)+1,99)))
    Attached Files Attached Files
    Last edited by AliGW; 12-15-2020 at 08:59 AM.

  8. #8
    Registered User
    Join Date
    11-10-2020
    Location
    Worthing
    MS-Off Ver
    2016
    Posts
    8

    Re: MAX function if part of number is constant

    Thankyou for your help, I must be doing something wrong.

    I have my numbers starting in cell X4 and stopping at X4000, and i need the current highest figure to update every time a new number is added. Hope I'm explaining this properly.

    Nick.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: MAX function if part of number is constant

    Try this:

    =MAX(IF(ISTEXT($X$4:$X$4000),--MID($X$4:$X$4000,FIND("/",$X$4:$X$4000)+1,99)))

    Are you entering it correctly so that curly brackets appear (using CTRL+SHIFT+ENTER)?

    Expand the range as far as you wish to cover future rows being added.

    Yes, you must be doing something wrong - I have shown that it should work. Short of seeing the file you are working on, I can only guess at what is wrong.
    Last edited by AliGW; 12-15-2020 at 10:29 AM.

  10. #10
    Registered User
    Join Date
    11-10-2020
    Location
    Worthing
    MS-Off Ver
    2016
    Posts
    8

    Re: MAX function if part of number is constant

    Thank you, it is now working.

    I forgot to enter with ctrl+shift+enter.

    Much appreciated.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: MAX function if part of number is constant

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: MAX function if part of number is constant

    If you don't like array formulae, use this instead:

    =AGGREGATE(14,6,MID($A$1:$A$20,SEARCH("/",$A$1:$A$20)+1,255)+0,1)

    change the bits in red to 5000, or whatever, to keep it future-proof.
    Glenn



  13. #13
    Registered User
    Join Date
    12-03-2020
    Location
    Bangkok,Thailand
    MS-Off Ver
    365
    Posts
    29

    Re: MAX function if part of number is constant

    Try this with CSE

    =MAX(--SUBSTITUTE($A$1:$A$3,"/","."))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Keeping formulas constant for only part of rows
    By Philip63 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-20-2018, 10:24 AM
  2. Replies: 2
    Last Post: 03-14-2015, 10:39 AM
  3. Remove the space between the variable and the constant part of a file name
    By SirRyanGiggs in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-08-2014, 02:08 AM
  4. [SOLVED] MID function: +0 modifier converting % symbol into part of the number
    By Duoae in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-21-2012, 05:30 AM
  5. [SOLVED] Keeping part of Formula constant whilst the rest changes.
    By Manuel Pedro in forum Excel General
    Replies: 2
    Last Post: 05-15-2012, 03:13 AM
  6. Replies: 2
    Last Post: 07-16-2009, 12:30 PM
  7. autofill with one part of the formula refering to a constant cell
    By old grey whiskers in forum Excel General
    Replies: 3
    Last Post: 08-19-2006, 01:25 AM

Tags for this Thread

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