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
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.
Posted by anon on May 03, 2001 6:32 AM
Array formula :-
=MAX(IF(A1:A5="car2",B1:B5))
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.
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)
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)
