Solved

Excel formula to get the max value based on a condition

Posted on 2007-03-22
1
1,233 Views
Last Modified: 2009-07-04
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
Comment
Question by:java-bean
1 Comment
 
LVL 1

Accepted Solution

by:
seistante earned 500 total points
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Word 2010 mail merge 3 32
Outlook 2010 Archive 3 35
Checkbox fires off a Statement 7 20
Save Email Attachments using VBA Excel 3 44
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now