MAX function for a given Position Title
 

Hello,
I have a 1000 line sheet that has multiple titles and so on. I like to
retrieve the highest salary for a given title. Position titles are not
sorted. Any suggestions on how this can be done?
example of the file:
MNTWKR $24500
SECY $21200
MNTWKR $30000
MNTWKR $28460

Appreciate any help on this.
Rich

MAX function for a given Position Title
 

Rich wrote...
I have a 1000 line sheet that has multiple titles and so on. I like to
retrieve the highest salary for a given title. Position titles are not
sorted. Any suggestions on how this can be done?
example of the file:
MNTWKR $24500
SECY $21200
MNTWKR $30000
MNTWKR $28460

If you have job titles in col A starting in A2 and corresponding
salaries in col B starting in B2, and you enter a job title in cell D2,
the following array formula will return the highest salary for that job
title.

=MAX(IF(A2:A1001=D2,B2:B1001))

MAX function for a given Position Title
 

For MIN try this:
D1: =MIN(IF(($A$1:$A$5=D1),$B$1:$B$5))

Note: commit that array formula by holding down the [Ctrl] and [Shift] keys
when you press [Enter]

Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"Rich" wrote:

Ron,
Thank You, this is very helpful. How about the MIN side? I inserted the
MIN instead of the MAX and it did not work? Any ideas?
Rich

"Ron Coderre" wrote:

With data in cells A1:B4, try this:

C1: MNTWKR
D1: =SUMPRODUCT(MAX(--($A$1:$A$4=C1)*$B$1:$B$4))


Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"Rich" wrote:

Hello,
I have a 1000 line sheet that has multiple titles and so on. I like to
retrieve the highest salary for a given title. Position titles are not
sorted. Any suggestions on how this can be done?
example of the file:
MNTWKR $24500
SECY $21200
MNTWKR $30000
MNTWKR $28460

Appreciate any help on this.
Rich

MAX function for a given Position Title
 

Non-array option:

=SUMPRODUCT(MAX((A2:A22=C1)*B2:B22))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Rich" wrote in message
...
Hello,
I have a 1000 line sheet that has multiple titles and so on. I like to
retrieve the highest salary for a given title. Position titles are not
sorted. Any suggestions on how this can be done?
example of the file:
MNTWKR $24500
SECY $21200
MNTWKR $30000
MNTWKR $28460

Appreciate any help on this.
Rich

MAX function for a given Position Title
 

Ron,
Thank You, this is very helpful. How about the MIN side? I inserted the
MIN instead of the MAX and it did not work? Any ideas?
Rich

"Ron Coderre" wrote:

With data in cells A1:B4, try this:

C1: MNTWKR
D1: =SUMPRODUCT(MAX(--($A$1:$A$4=C1)*$B$1:$B$4))


Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"Rich" wrote:

Hello,
I have a 1000 line sheet that has multiple titles and so on. I like to
retrieve the highest salary for a given title. Position titles are not
sorted. Any suggestions on how this can be done?
example of the file:
MNTWKR $24500
SECY $21200
MNTWKR $30000
MNTWKR $28460

Appreciate any help on this.
Rich

MAX function for a given Position Title
 

With data in cells A1:B4, try this:

C1: MNTWKR
D1: =SUMPRODUCT(MAX(--($A$1:$A$4=C1)*$B$1:$B$4))


Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"Rich" wrote:

Hello,
I have a 1000 line sheet that has multiple titles and so on. I like to
retrieve the highest salary for a given title. Position titles are not
sorted. Any suggestions on how this can be done?
example of the file:
MNTWKR $24500
SECY $21200
MNTWKR $30000
MNTWKR $28460

Appreciate any help on this.
Rich

MAX function for a given Position Title
 

Ron,
Yes, it does work when I did [Ctrl] and [Shift] keys when I pressed [Enter].
I gather that {} indicates array formula? Not sure if I understand that.

Otherwise,
Thanks for your help. A big help for my project.
Rich

"Ron Coderre" wrote:

For MIN try this:
D1: =MIN(IF(($A$1:$A$5=D1),$B$1:$B$5))

Note: commit that array formula by holding down the [Ctrl] and [Shift] keys
when you press [Enter]

Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"Rich" wrote:

Ron,
Thank You, this is very helpful. How about the MIN side? I inserted the
MIN instead of the MAX and it did not work? Any ideas?
Rich

"Ron Coderre" wrote:

With data in cells A1:B4, try this:

C1: MNTWKR
D1: =SUMPRODUCT(MAX(--($A$1:$A$4=C1)*$B$1:$B$4))


Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"Rich" wrote:

Hello,
I have a 1000 line sheet that has multiple titles and so on. I like to
retrieve the highest salary for a given title. Position titles are not
sorted. Any suggestions on how this can be done?
example of the file:
MNTWKR $24500
SECY $21200
MNTWKR $30000
MNTWKR $28460

Appreciate any help on this.
Rich

MAX function for a given Position Title
 

Thank you Ron. This appears to be a great help! One more quick one, why
about the MIN too? I inserted MIN and it did not work. Any ideas?

"Ron Coderre" wrote:

With data in cells A1:B4, try this:

C1: MNTWKR
D1: =SUMPRODUCT(MAX(--($A$1:$A$4=C1)*$B$1:$B$4))


Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"Rich" wrote:

Hello,
I have a 1000 line sheet that has multiple titles and so on. I like to
retrieve the highest salary for a given title. Position titles are not
sorted. Any suggestions on how this can be done?
example of the file:
MNTWKR $24500
SECY $21200
MNTWKR $30000
MNTWKR $28460

Appreciate any help on this.
Rich