Conditional Max

  • Thread starter Thread starter MLS
  • Start date Start date
M

MLS

Hi,

I am trying to get a max age from a range of cells, however the result is
depending on the value in another cell. The range looks like this:

ID BU Age
11348 JPU 44
12479 FKG 46
12487 LPS 27
14539 LPS 32
17583 FKG 24
39175 LPS 34
45867 JPU 42
74583 FKG 39

I need a formula that will give me the max age in the following way:
FKG 46
JPU 44
LPS 34

Is there a way to do this without using a pivot table?

Many thanks,
MLS
 
Hi,

Try this ARRAY formula. See below on how to enter it

=MAX(IF(B1:B8="FKG",C1:C8))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike
 
The formula I used was: =MAX(IF(B2:B9="FKG",C2:C9))

The only change I made from the formula you posted was to not include the
header row (Row 1) and instead include the bottom row (Row 9).

MLS
 
Mike has suggested you an array formula..

An array formula can perform multiple calculations and then return either a
single result or multiple results. Array formulas act on two or more sets of
values known as array arguments. Each array argument must have the same
number of rows and columns. You create array formulas in the same way that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula>}"

If this post helps click Yes
 
Back
Top