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
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
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!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks for the reply, this seems to bring back a value of 0.
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 121/001 3 221/002 321/003
Sheet: Sheet1
If you don't understand my last post, please see the attachment for clarification.
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
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)))
Last edited by AliGW; 12-15-2020 at 08:59 AM.
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.
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.
Thank you, it is now working.
I forgot to enter with ctrl+shift+enter.
Much appreciated.
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.
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
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Try this with CSE
=MAX(--SUBSTITUTE($A$1:$A$3,"/","."))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks