Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Excel formula to get the max value based on a condition

Posted on 2007-03-22
Medium Priority
1,244 Views
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
Question by:java-bean
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
1 Comment

LVL 1

Accepted Solution

seistante earned 2000 total points
ID: 18769732
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…