Associating highest value with name
 

I am trying to write a formula (no luck so far) that will find the highest
value in a list of numbers and then associate it with an office name in
column A. The problem I'm having is that the totals are way over in column
AQ. I then want to transfer the office name to one cell and the value to
another like OFFICE NAME: and then AMOUNT: (and paste the values into a blank
cell next to it). Anyone?

Associating highest value with name
 

Hi!

Try this:

=INDEX(A1:A10,MATCH(MAX(AQ1:AQ10),AQ1:AQ10,0))

Note: if there are multiple instances of MAX the formula will match the
first instance.

Biff

"mailrail" wrote in message
...
I am trying to write a formula (no luck so far) that will find the highest
value in a list of numbers and then associate it with an office name in
column A. The problem I'm having is that the totals are way over in column
AQ. I then want to transfer the office name to one cell and the value to
another like OFFICE NAME: and then AMOUNT: (and paste the values into a
blank
cell next to it). Anyone?

Associating highest value with name
 

Biff:

Perfect! Thanks, man!

"Biff" wrote:

Hi!

Try this:

=INDEX(A1:A10,MATCH(MAX(AQ1:AQ10),AQ1:AQ10,0))

Note: if there are multiple instances of MAX the formula will match the
first instance.

Biff

"mailrail" wrote in message
...
I am trying to write a formula (no luck so far) that will find the highest
value in a list of numbers and then associate it with an office name in
column A. The problem I'm having is that the totals are way over in column
AQ. I then want to transfer the office name to one cell and the value to
another like OFFICE NAME: and then AMOUNT: (and paste the values into a
blank
cell next to it). Anyone?

Associating highest value with name
 

Oh...one other thing: what about the LOWEST or LEAST contributor in the
column? How do I sort that out? Also, I don't want to count cells that have a
zero value. Is there a way?

"mailrail" wrote:

I am trying to write a formula (no luck so far) that will find the highest
value in a list of numbers and then associate it with an office name in
column A. The problem I'm having is that the totals are way over in column
AQ. I then want to transfer the office name to one cell and the value to
another like OFFICE NAME: and then AMOUNT: (and paste the values into a blank
cell next to it). Anyone?

Associating highest value with name
 

=INDEX(A1:A10, MATCH(MIN(IF(AQ1:AQ10,AQ1:AQ10,"")),AQ1:AQ10,0))

Commit with Control+Shift+Enter after typing/pasting it into your formula bar.

"mailrail" wrote:

Oh...one other thing: what about the LOWEST or LEAST contributor in the
column? How do I sort that out? Also, I don't want to count cells that have a
zero value. Is there a way?

"mailrail" wrote:

I am trying to write a formula (no luck so far) that will find the highest
value in a list of numbers and then associate it with an office name in
column A. The problem I'm having is that the totals are way over in column
AQ. I then want to transfer the office name to one cell and the value to
another like OFFICE NAME: and then AMOUNT: (and paste the values into a blank
cell next to it). Anyone?

Associating highest value with name
 

Here's a non-array alternative as long as none of the values are negative:

=INDEX(A1:A10,MATCH(SMALL(AQ1:AQ10,COUNTIF(AQ1:AQ1 0,0)+1),AQ1:AQ10,0))

Biff

"mailrail" wrote in message
...
Oh...one other thing: what about the LOWEST or LEAST contributor in the
column? How do I sort that out? Also, I don't want to count cells that
have a
zero value. Is there a way?

"mailrail" wrote:

I am trying to write a formula (no luck so far) that will find the
highest
value in a list of numbers and then associate it with an office name in
column A. The problem I'm having is that the totals are way over in
column
AQ. I then want to transfer the office name to one cell and the value to
another like OFFICE NAME: and then AMOUNT: (and paste the values into a
blank
cell next to it). Anyone?