MAX function if part of number is constant

1. 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. Re: MAX function if part of number is constant

Try this:

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

3. Re: MAX function if part of number is constant

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

4. 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. Re: MAX function if part of number is constant

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

6. 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. Re: MAX function if part of number is constant

Try this;

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

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. 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.

10. 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. Re: MAX function if part of number is constant

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. 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.

13. Re: MAX function if part of number is constant

Try this with CSE

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

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