Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

find maximum value based on criterion in another column

Posted by richard kortwijk on May 03, 2001 5:52 AM
in excel 97 i'm looking for a formula that calculates the maximum value of a column with a specified criterion. for example i want to have the maximum value of car2 in the following chart (=75):
car3 100
car2 50
car3 25
car2 25
car2 75
can anyone of you experts help me out? Many Thanks

Check out our Excel Resources

Re: find maximum value based on criterion in another column

Posted by Mark W. on May 03, 2001 6:30 AM
With your values in cells A1:B5 use the following
array formula:

{=MAX((A1:A5="car2")*B1:B5)}

Note: Array formulas are entered using the
Control+Shift+Enter key combination. The braces,
{}, are supplied by Excel and are indicative of
the nature (array) of the formula.


Re: find maximum value based on criterion in another column

Posted by anon on May 03, 2001 6:32 AM

Array formula :-
=MAX(IF(A1:A5="car2",B1:B5))



Re: find maximum value based on criterion in another column

Posted by richard kortwijk on May 09, 2001 5:23 AM
Hi:

Thank you for your response.

I have no idea how to write a code for this. My knowledge at writing on BVA is very poor. Let me tell you however, the tables should be synchronize not only by the item "cab" but by any in a list of around ten items in the drop down lists in each of the pivot table. Do you insist you can handle this? Thank you.

Nona.


Re: find maximum value based on criterion in another column

Posted by richard kortwijk on May 09, 2001 5:24 AM
both of your options work fine!!!!
thank you very much

richard
(reason for delay: i was on a holliday)


Re: find maximum value based on criterion in another column

Posted by richard kortwijk on May 09, 2001 5:26 AM
both of your options work fine!!!!
thank you very much

richard
(reason for delay: i was on a holliday)


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.