# A formula to calculate the MAX number in a range between two numbers.

1. ## A formula to calculate the MAX number in a range between two numbers.

Hi everyone,

I was wondering if there is a formula to calculate the MAX number in a range but limit the search with certain identifiers.

For example; lets say the list looks like this:
3101
3102
3103
3201
3202
3203
3301
3302
3303

I want to select this range but only find the MAX number between say 3100-3199, and then do the same for 3200-3299 and 3300-3399 etc.

Edit: OR "MAX number from all numbers starting with 31,32,33 etc"

Is this possible? I tried googling but couldn't find anything for this specific formula.  Register To Reply

2. ## Re: A formula to calculate the MAX number in a range between two numbers.

As the XL version in your profile is unknown to me (2105??) check your version and see if you have the MAXIFS function  Register To Reply

3. ## Re: A formula to calculate the MAX number in a range between two numbers.

Version 2105 (Build 14026.20270) (Microsoft® Excel® for Microsoft 365 MSO (16.0.14026.20270) 64-biters)

Looks like i do have that function yes.  Register To Reply

4. ## Re: A formula to calculate the MAX number in a range between two numbers.

OK, so it's XL365. You should change your profile accordingly as 365 has some features other versions don't have.  Register To Reply

5. ## Re: A formula to calculate the MAX number in a range between two numbers.

Thanks, I update my profile. Any idea if a formula like this is possible? I had a look at MAXIFS but couldn't figure out how to set the criteria if it comes from the same range/column.  Register To Reply

6. ## Re: A formula to calculate the MAX number in a range between two numbers.

Enter your numbers in A1 to A9

then

=MAX(IF(\$A\$1:\$A\$9>=3100,1,0)*IF(\$A\$1:\$A\$9<=3200,1,0)*\$A\$1:\$A\$9)  Register To Reply

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