# Finding Max number within a specific range of numbers

1. ## Finding Max number within a specific range of numbers

I have a file where I track work order numbers. They are assigned a tracking number by area 1XXXX for area 1, 2XXXX for area 2, etc. I have the top of the sheet frozen so I can see the lates number assigned by area but I am currently updating that manually. Is there a way to have excel look at the list of all work orders and display the max between 10000 and 19999?  Register To Reply

2. ## Re: Finding Max number within a specific range of numbers

*latest (just a correction)  Register To Reply

3. ## Re: Finding Max number within a specific range of numbers

I'm thinking =LARGE(IF( "number from list" <20000),1)

but i don't know how to input the number from the list as a whole array of numbers. This would be for area 1 by the way ^  Register To Reply

4. ## Re: Finding Max number within a specific range of numbers

Another problem is I wouldn't know what to use for the values if it fails or passes the logic test...  Register To Reply

5. ## Re: Finding Max number within a specific range of numbers

See formula below. I used A4:A20 as my sample range of test data, so you will just need to adjust that based on your actual range.

=MAX(IF(LEFT(A4:A20,1)="1",A4:A20))

Enter this formula as an array by using Ctrl+Shift+Enter rather than just Enter. It will look like this if you've entered it correctly (notice the extra brackets):

{=MAX(IF(LEFT(A4:A20,1)="1",A4:A20))}

This matches the left character from the tracking number from whichever group you want to pull the max number. The formula for the tracking numbers starting with "2" will look like this:

{=MAX(IF(LEFT(A4:A20,1)="2",A4:A20))}

Hope this helps.  Register To Reply

6. ## Re: Finding Max number within a specific range of numbers

Thank you, That worked perfectly!  Register To Reply

7. ## Re: Finding Max number within a specific range of numbers

Hi can't get the problem. can anyone provide me the sample sheet so that i can understand this please.

Thanks.
Perpectuals  Register To Reply

8. ## Re: Finding Max number within a specific range of numbers

Attached is the sample workbook that I used.  Register To Reply

9. ## Re: Finding Max number within a specific range of numbers

If you using Excel 2010 version and up you can take advantage of AGGREGATE function for array formulas that do not require Ctrl+Shift+Enter confirmation.

=AGGREGATE(14,6,A5:A21/((--LEFT(A5:A21)=1)),1)

or this formula that will be compatible with excel versions prior to 2010 version.

=MAX(INDEX((--LEFT(A5:A21)=1)*A5:A21,0))

***change =1 in formulas to =2 to get the second MAX value  Register To Reply

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