Excel formula to get the max value based on a condition
Posted on 2007-03-22
I have a list of values in Excel as follows.
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