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

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

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.

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.

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.

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)

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