Ask Your Question
0

How to write a formula to find largest number?

asked 2013-12-09 00:22:49 +0100

JDavid gravatar image

In column A I have a list of names. In column B I have a list of dollar amounts that each name in column A earned. I would like a formula that can find the largest amount in column B and then display the name from column A of the person who earned that amount.

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted
1

answered 2015-06-11 14:21:10 +0100

pierre-yves samyn gravatar image

Hi

There is no need for macro or rearrange data. With Names in A and Amounts in B, to find first & second use formulas like:

=INDEX(A2:A100;MATCH(LARGE(B2:B100;1);B2:B100;0))
=INDEX(A2:A100;MATCH(LARGE(B2:B100;2);B2:B100;0))

See LargestNumber.ods

Regards

edit flag offensive delete link more

Comments

Brilliant! Thank you for a valuable formula!

Pedro gravatar imagePedro ( 2015-06-28 11:47:58 +0100 )edit
0

answered 2015-06-11 13:57:20 +0100

JKEngineer gravatar image

If it works with your workflow, you could sort the contents of Cols A and B by the contents of B in descending order. Then just look at it. It's not elegant, it is manual, but it should work.

edit flag offensive delete link more
0

answered 2013-12-09 00:42:09 +0100

Pedro gravatar image

If you have the other way around (i.e. values in Col A and names in Col B) it is quite easy: in cell C1 use function =MAX(A1:A10) and in cell D1 use function =VLOOKUP(C1; A1:B10;2)

Explanation: The second function is looking for the MAX value (C1) and getting from the block A1:B10 the corresponding name from column 2. There is one limitation to this method: if there are two people who have the same value, the result will be the first on the list because the search is carried from top to bottom...

Hope this helps ;)

edit flag offensive delete link more

Comments

Can we add macros. i also need to find the second biggest number..

umarphysics gravatar imageumarphysics ( 2014-10-01 07:28:28 +0100 )edit
0

answered 2015-06-12 12:31:12 +0100

Lupp gravatar image

updated 2015-06-12 12:45:10 +0100

We should also consider that the "largest" value may occur more than once. The maximum number of occurrences of the maximum value is the total size of the sample. Using a lookup based on the rank of a value with respect to a sample must therefore always thoroughly consider if it makes sense at all.

Another aspect should be the inefficiency of LARGE (or similar approaches) which may be of some importance in cases where many cells are meant to deliver information related to different ranks with respect to a large sample. The function has to examine the complete sample anew for every call.

Creating a sorted report (It may be placed anywhere by the 'Data' > 'Sort' tool, not overwriting the original data this way.) as @JKEngineer suggested would avoid both the mentioned problems.

A sorted output based on formuale should also not be less efficient than using LARGE for the complete range of ranks. And it would also avoid the issue of many equally ranking values and the erroneous lookup results caused by it.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2013-12-09 00:22:49 +0100

Seen: 12,911 times

Last updated: Jun 12 '15