Max(if) to return another columns value

[confused in Frankfurt]

Hi There,
I have a problem I need to not only return the highest value per sales rep but the name of the deal.

[TABLE="class: grid, width: 240"]
<tbody>[TR]
[TD="width: 80"]Opportunity Name
[/TD]
[TD="width: 80"]Opportunity Owner[/TD]
[TD="width: 80"]Unique Sales Price[/TD]
[/TR]
[TR]
[TD="width: 80, align: left"]Arriva_Mittelstand[/TD]
[TD="width: 80, align: left"]Andrea Junge[/TD]
[TD="align: right"]130.000[/TD]
[/TR]
[TR]
[TD="width: 80, align: left"]Siemens-CIT-ARIS Design Server[/TD]
[TD="width: 80, align: left"]Denis Wachsmuth[/TD]
[TD="align: right"]17.600[/TD]
[/TR]
[TR]
[TD="width: 80, align: left"]Telefónica Alfabet[/TD]
[TD="width: 80, align: left"]Dirk Klimas[/TD]
[TD="align: right"]500.000[/TD]
[/TR]
[TR]
[TD="width: 80, align: left"]DB Systel_Terminalemulation[/TD]
[TD="width: 80, align: left"]Andrea Junge[/TD]
[TD="align: right"]800.000[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl258, width: 56, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

The following formula returns the highest deal in the portfolio but how to get the name of the deal is the problem.
=MAXA(if('Pipeline SF '!H:H=Highlights!$B60;'Pipeline SF '!AI:AI;0))

Regards
Sarah

 

[Momentman]

Here's my set-up

Excel 2010
	A	B	C
1	Opportunity Name	Opportunity Owner	Unique Sales Price
2	Arriva_Mittelstand	Andrea Junge	130
3	Siemens-CIT-ARIS Design Server	Denis Wachsmuth	17.6
4	Telefnica Alfabet	Dirk Klimas	500
5	DB Systel_Terminalemulation	Andrea Junge	800
6
7	OWNER	NAME	MAXIMUM
8	Andrea Junge	DB Systel_Terminalemulation	800
Sheet1

Cell Formulas
Range	Formula
B8	{=INDEX($A$2:$A$5,MATCH(2,(B2:B5=$A$8)+(C2:C5=$C$8),0))}
C8	{=MAX(IF(B2:B5=A8,C2:C5))}
Press CTRL+SHIFT+ENTER to enter array formulas.

The "2" in the MATCH formula is based on the fact that if the two condition are met i.e NAME and Value, you have TRUE+TRUE which is 2 (something like that)

 

[confused in Frankfurt]

Hi what happens if the returned max value is available twice or more?

 

[Momentman]

Can the maximum value appear more than once for each owner? e.g can you have Andrew Junge, 800 and on another line Andrew Junge, 800?

My formula is matching both the name and the max value for that specific name

 

[Canapone]

Hi,


in the example: in C1:C1000 names of the deal

=INDEX('Pipeline SF '!C1:C1000;MATCH(MAXA(if('Pipeline SF '!H:H=Highlights!$B60;'Pipeline SF '!AI1:AI1000;0))&Highlights!$B60;'Pipeline SF '!AI1:AI1000&'Pipeline SF '!H1:H1000;0))

to be confirmed with control+shift+enter

You'll have for sure to adjust formula references.

I hope it helps

 

[Momentman]

In the event there are multiple matches, you could use the array formula in E2 and drag downwards

Excel 2010
	A	B	C	D	E
1	Opportunity Name	Opportunity Owner	Unique Sales Price		MATCHES
2	Arriva_Mittelstand	Andrea Junge	800		Arriva_Mittelstand
3	Siemens-CIT-ARIS Design Server	Denis Wachsmuth	17.6		DB Systel_Terminalemulation
4	Telefnica Alfabet	Dirk Klimas	500
5	DB Systel_Terminalemulation	Andrea Junge	760
6	Siemens-CIT-ARIS Design Server	Denis Wachsmuth	17.6
7	Telefnica Alfabet	Dirk Klimas	500
8	DB Systel_Terminalemulation	Andrea Junge	800
9
10
11	OWNER	MAXIMUM
12	Andrea Junge	800
Sheet1

Cell Formulas
Range	Formula
E2	{=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$A$12,IF($C$2:$C$8=$B$12,ROW($A$2:$A$8)-ROW($A$2)+1)),ROW(A1))),"")}
B12	{=MAX(IF(B2:B8=A12,C2:C8))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[vds1]

Alternatively you can have pivot table. Select column A , B , C and go to insert pivot table. Follow the instruction as per your needs. Remember that Pivot can be in existing worksheet.


1. Move Unique sales price to ∑ values
2. Move Opportunity Name to Row Labels
3. Under ∑ values you may see the count, so select the drop down | Value field settings | select sum
4. You will see row labels on the table, select drop down | Value Filters | Top 10 | select 1 instead of 10.