Prev: I need help with a macro please!
Next: Formula Help
From: Sasikiran on 18 May 2010 04:35
Dear,

I am looking for a formula which would give the max value of each city in
column D based on the entry's in column B.

Please help.

Col A Col B Col C Col D
Texas 76 Texas =
Detroit 252 Detroit =
Detroit 29 Tenesse
Texas 443 Mexico
Texas 0 Chicago
Texas 700
Detroit 0
Tenesse 51
Tenesse 70
Texas 673
Tenesse 90
Mexico 21
Chicago 10
From: Mike H on 18 May 2010 05:44
Hi,

Put this ARRAY formula in d1, array enter it (see below) and drag down

=MAX(IF($A$1:$A$13=C1,$B$1:$B$13))

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

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Sasikiran" wrote:

> Dear,
>
> I am looking for a formula which would give the max value of each city in
> column D based on the entry's in column B.
>
> Please help.
>
> Col A Col B Col C Col D
> Texas 76 Texas =
> Detroit 252 Detroit =
> Detroit 29 Tenesse
> Texas 443 Mexico
> Texas 0 Chicago
> Texas 700
> Detroit 0
> Tenesse 51
> Tenesse 70
> Texas 673
> Tenesse 90
> Mexico 21
> Chicago 10
From: Sasikiran on 18 May 2010 06:46
Thank you Mike :)

"Mike H" wrote:

> Hi,
>
> Put this ARRAY formula in d1, array enter it (see below) and drag down
>
> =MAX(IF($A$1:$A$13=C1,$B$1:$B$13))
>
> 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
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Sasikiran" wrote:
>
> > Dear,
> >
> > I am looking for a formula which would give the max value of each city in
> > column D based on the entry's in column B.
> >
> > Please help.
> >
> > Col A Col B Col C Col D
> > Texas 76 Texas =
> > Detroit 252 Detroit =
> > Detroit 29 Tenesse
> > Texas 443 Mexico
> > Texas 0 Chicago
> > Texas 700
> > Detroit 0
> > Tenesse 51
> > Tenesse 70
> > Texas 673
> > Tenesse 90
> > Mexico 21
> > Chicago 10
 | 
Pages: 1
Prev: I need help with a macro please!
Next: Formula Help