Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Thread: search for maximum value in a column

1. kiz
Member
Join Date
16th October 2005
Posts
29

search for maximum value in a column

hi, i'm new to this and need your help.

i have a simple excel sheet where col A values are names,
and col B values are the amount in dollars.
how can i search for the max value in column B and show who that is from col A.

thanks

Excel Video Tutorials / Excel Dashboards Reports

2. Re: excel help search

Hi and welcome to OzGrid.

It's possible that more than 1 person in Column A has the maximum amount in Column B. Do you want all persons or any person?

3. Re: excel help search

I've moved your thread to the proper forum. Please ensure that you post to the correct or most appropriate forum.

Do you want an Excel or VBA solution?

4. kiz
Member
Join Date
16th October 2005
Posts
29

Re: excel help search

thanks,
it may be possible that more than 1 person has the same max value,

thanks again

Excel Video Tutorials / Excel Dashboards Reports

5. kiz
Member
Join Date
16th October 2005
Posts
29

Re: excel help search

if possible i would prefer an excel solution.
thanks

Excel Video Tutorials / Excel Dashboards Reports

6. Re: excel help search

Kiz,

excel help search is not very helpful, so I have edited it.

This helps people to help you and others find solutions to their problems.

Thanks.

7. Re: search for maximum value in a column

If you were just searching for the maximum in column B the formula would be:
VB:
```=MAX(B2:B7)

```
and if you wanted to know how many persons in column A had the maximum in column B:
VB:
```=COUNTIF(B2:B7,MAX(B2:B7))

```
I've used a Pivot Table to look at columns A and B and show me the persons with the maximum. When you've added more data or edited the data in columns A and B, right-click on any cell in the Pivot Table and click Refresh Data to update the Pivot Table.

You see as you hang out here that there are often more than 1 way to do something.

Using VBA, you could have a worksheet change event macro that could run certain code whenever a cell in column A or B changed.

This could be used to update the pivot table everytime a cell in either column A or B changed.

Alternatively, the code could list the persons in column D whose amount in column B is equal to the maximum in column B.

8. kiz
Member
Join Date
16th October 2005
Posts
29

Re: search for maximum value in a column

thanks it's a great help.
what i need is to get the name of the person with max value,and
then display the name in the worksheet, so that say in
col C i have the name and col D the amount of the revenue.
chances are there will be no 2 dollar amounts equal.

thanks

Excel Video Tutorials / Excel Dashboards Reports

9. Re: search for maximum value in a column

Let's assume your names are in A2:A5, with the values in B2:B5

Use

=INDEX(\$A\$2:\$B\$5,MATCH(MAX(\$B\$2:\$B\$5),\$B\$2:\$B\$5,0),1)

To get the max name - this will work for ONE maximum value

For multiple instances, the first name will be returned, but you could use barb's contif founction to provide a warning for when this happens.

10. Super Moderator
Join Date
26th January 2003
Location
Derbyshire,UK
Posts
19,467

Re: search for maximum value in a column

If the dollar amounts are all different doesn't Barbara's Pivottable do that?

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno