[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1247
  • Last Modified:

Excel formula to get the max value based on a condition

Hi,

I have a list of values in Excel as follows.

10506
10810
21085
10259
20735
31082
20133
31000
10616
10084
20515
30365
10395

The number range from 10000 to 40000. I need a formula that will calculate the max value of the list based on a given condition. For example, I want the maximum value of all the values between 10000 and 20000. I've tried using combination of IF and MAX functions without much success. I guess what I need is something like a MAXIF function (similar to COUNTIF and SUMIF). Sorting based on the number is not an option as this list is constantly updated and the formula needs to robust enough to handle this without requiring a resort.

Appreciate any ideas you guys may have. Thanks
0
java-bean
Asked:
java-bean
1 Solution
 
seistanteCommented:
Hi, Java-bean

Assuming your range is in column A ,
On  column B enter following,
( (A1>=10000)*(A1<=20000) ) and copy down...
On Column C enter following
=B1*A1 and copy down
From Column C extract,  Max

Hope this helps

Pedro

Pedro
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now