PDA

View Full Version : Formula Returning Tree With Largest Profit



macro_man
03-04-2011, 10:29 AM
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

Bob Phillips
03-04-2011, 12:46 PM
They are called array formulae, and they are consistently accurate (or consistently inaccurate, they are consistent); it is the people who change them that are sometimes not consistently accurate.

shrivallabha
03-04-2011, 09:49 PM
Suppose your trees are listed in column A and their profits in column B then try formula given below:


=INDIRECT("A"&MATCH(MAX(B1:B6),B1:B6,0))