PDA

View Full Version : Solved: Top 3 highest values in range



LinkND
05-29-2008, 02:22 AM
In a sheet I want to filter my data and pick out the top 3 with the highest incomes. I only want to look for the highest incomes of people who are paid. The result needs to positioned in a new sheet.

Working with pivot tables is nice, but those are only handy if your list is fixed without blank cells. I had it working somehow, but only by using a formula with MAX and LARGE in a matrix.

In the attachment you can see what I mean =)

xld
05-29-2008, 02:49 AM
Your income cells are text not numbers. It would be simple with a formula if they were numeric.

xld
05-29-2008, 02:53 AM
BTW, the 3rd highest is person F not A.

LinkND
05-29-2008, 02:59 AM
Ah, yes... you are right ;) I changed it by hand (the F), but you can delete the $ before the ammount of money to make it a number. The dollar sign isn't necessary to be honest.

xld
05-29-2008, 03:01 AM
Okay.

I3: =LARGE(IF((ISNUMBER(D4:D44))*(C4:C44="Yes"),D4:D44),ROW(A1))
K3: =INDEX(B1:B43,MATCH(L3,D2:D44,0))

and copy down.

I3 is an array formula.

Note how the ranges in the second formula are offset.

LinkND
05-29-2008, 03:50 AM
Could you add this formula to an attachment for me? Because I am always getting formula errors in my own sheets -__-"

EDIT: Don't mind my last question =) it already works ^^ thank you.