Solved

Excel formula to get the max value based on a condition

Posted on 2007-03-22
1
1,236 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
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 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