I have a list of sales people and a list of their sale revenues in two separate columns. How do I use an advanced filter or other sorting means to find the max of the sale revenue column and then have the formula output be the corresponding sales person?
+1
A:
Referencing this page: http://www.techonthenet.com/excel/formulas/max.php
In this example, assuming column A was your list of salespeople and column B was your list of sales revenues...
=Max(B2:B6)
in any empty cell would return the highest sales revenue from column B.
David
2010-03-29 05:53:55
I understand how to use a =max() function to return the highest value (in your link). But how can I tell the formula to give me the corresponding date instead, not the value. I want to use the max of the values to have the formula output the corresponding date.Thanks for your help.
Adam
2010-03-29 07:19:43
A:
I figured it out.
You first need to set a criteria. In the link's example, you would first (off to the side) make a small column that had a title of Value, then right under that put in the function =Max(B2:B6). Then click the "Advanced Filter" button. The data range would be the entire database, A1:B6. The criteria range is the new two row column you just made of Value and the Max formula. Then select and output range that will be big enough to hold your filtered data. in this case, a 2x2 grid will be enough. (Make sure to click the copy to new cell option at the top.)
The resulting filter will be the Date of the Max Value.
This is my first answer post on this site, so please let me know if I formatted it wrong.
Adam
2010-03-29 08:36:36