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 =)
Your income cells are text not numbers. It would be simple with a formula if they were numeric.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
BTW, the 3rd highest is person F not A.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Ah, yes... you are rightI 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.
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.
Last edited by Bob Phillips; 05-29-2008 at 03:17 AM.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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.
Last edited by LinkND; 05-29-2008 at 04:33 AM.
Posting Permissions
Reply With Quote
