Formula Returning Tree With Largest Profit

[sacman]

I am posting a question using one of the examples from Excel's help files with this stipulation: I do not want a formula that requires the Control+Shift+Enter as those types have not been consistently accurate. I'm not sure what that type of formula is called.

Is there a formula or a combination of formulas that will identify the apple tree with the maximum profit in the following chart? The DMAX returns a value but I cannot locate a funtion that will identify the actual item (as a text) in a database.


Tree Profit
Apple A 105
Pear A 96
Cherry A 105
Apple B 75
Pear B 9 76.8
Apple C 45

Obviously the answer I want returned by the formula in this example would be Apple-A but the application for this is much larger. I wanted to phrase my question in the simplest manner as I tend to overcomplicate things .

Sorry about the poorly written chart. I tried several ways to copy/paste but the result was always a concatinated mess of text.

Allen

 

[rpadminserver]

Where you description is column A and you count is column B

=INDEX(A:A,MATCH(DMAX(A1:B7,"CNT",B1:B7),B1:B7),0)

One downside.... Possible problem if you have 2 of the same counts (column b)

CNT is the heading I put above column B ("name" is what I used for column a)

Name CNT
apple a 135
pear a 96
cherry a 112
apple b 75
pear b 76.8
apple c 45

 

[sacman]

Where you description is column A and you count is column B

=INDEX(A:A,MATCH(DMAX(A1:B7,"CNT",B1:B7),B1:B7),0)

One downside.... Possible problem if you have 2 of the same counts (column b)

CNT is the heading I put above column B ("name" is what I used for column a)

Name CNT
apple a 135
pear a 96
cherry a 112
apple b 75
pear b 76.8
apple c 45

Thanks very much for your help but I made a mistake in my original post. I'll need to rephrase my question: In the following table which of each tree has the maximum profit?

<TABLE style="WIDTH: 170pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=227><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><TBODY><TR style="HEIGHT: 18.75pt; mso-height-source: userset" height=25><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 48pt; HEIGHT: 18.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #ece9d8" class=xl75 height=25 width=64>Tree</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f3f3f3; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #ece9d8" class=xl76 width=64>Part</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f3f3f3; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #ece9d8" class=xl76 width=64>Profit</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f3f3f3; WIDTH: 26pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl77 width=35></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72 height=18 width=64>Apple</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=64>A</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=64 align=right>105</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 26pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=35></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #ece9d8" class=xl73 height=18 width=64>Apple</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f3f3f3; WIDTH: 48pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #ece9d8" class=xl65 width=64>B</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f3f3f3; WIDTH: 48pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #ece9d8" class=xl65 width=64 align=right>75</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f3f3f3; WIDTH: 26pt; BORDER-TOP: #cccccc; BORDER-RIGHT: windowtext 1pt solid" class=xl68 width=35></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl78 height=18 width=64>Apple</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f3f3f3; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=64>C</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f3f3f3; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=64 align=right>45</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 26pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=35></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #ece9d8" class=xl79 height=18 width=64>Cherry</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #ece9d8" class=xl80 width=64>A</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #ece9d8" class=xl80 width=64 align=right>105</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f3f3f3; WIDTH: 26pt; BORDER-TOP: #cccccc; BORDER-RIGHT: windowtext 1pt solid" class=xl68 width=35></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72 height=18 width=64>Pear</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=64>A</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=64 align=right>76.8</TD><TD style="BORDER-BOTTOM: #cccccc 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 26pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=35></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #ece9d8" class=xl74 height=18 width=64>Pear</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f3f3f3; WIDTH: 48pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #ece9d8" class=xl70 width=64>B</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f3f3f3; WIDTH: 48pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #ece9d8" class=xl70 width=64 align=right>96</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #f3f3f3; WIDTH: 26pt; BORDER-TOP: #cccccc; BORDER-RIGHT: windowtext 1pt solid" class=xl71 width=35></TD></TR></TBODY></TABLE>

Obviously the answer for the Apple tree is A but what about the Pear tree? Again, this will apply to more realistic situation.

My apologies for not asking the correct question to begin with.

Allen

 

[rpadminserver]

Ok... I see...
Have you thought about using a pivot chart?

 

[sacman]

Ok... I see...
Have you thought about using a pivot chart?

Thanks. I had thought about that would prefer not to. I may end up doing that as a last resort (I am a complete ignoramous when it comes to pivot charts).

Allen

 

[sacman]

Ok... I see...
Have you thought about using a pivot chart?

Hello again. I created a pivot chart to the best of my ability but it is giving me the same results as the typical dmax function: only the maximum value is displayed, not the actual type of tree (i.e. text) with the maximum value.

Allen

 

[tushiroda]

try the below:

Excel Workbook
	A	B	C	D	E	F
1	Apple	A	105		Apple	A
2	Apple	B	75		Cherry	A
3	Apple	C	45		Pear	B
4	Cherry	A	105
5	Pear	A	76.8
6	Pear	B	96
Sheet3

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[sacman]

try the below:

Excel Workbook
	A	B	C	D	E	F
1	Apple	A	105		Apple	A
2	Apple	B	75		Cherry	A
3	Apple	C	45		Pear	B
4	Cherry	A	105
5	Pear	A	76.8
6	Pear	B	96
Sheet3

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

I had asked for a formula other than an array type (see initial post). I find these to be inaccurate (I had one similar to this that gave a correct answer for some but not for all of the parts).
Thanks.
Allen

 

[sacman]

Nonetheless, I guess I am destined to learn more about the array formulas so that I can troubleshoot them.

Thanks for all of your help.

Allen

 

[tushiroda]

ah, I missed the initial bit about the array formulas, though if the results were inaccurate, it was due to a poorly setup formula vs. any real accuracy issues with array formulas themselves.