• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Return bottom 3 values from an array/table based on a criteria?

J

JungleJme

New Member
Hi!


Got a bit of a head scratcher here. I have list of engineers, by region and a "score" for each.


I want a formula that will return the three worst performing engineers for whatever region the user selects.


I have tried various formula using small, index & match but am completely stuck! I even tried the dreaded SUMPRODUCT (my personal nemesis) but couldn't get it to work.


An example is here and its just an .xls with a fake sample of data in it....


http://dropcanvas.com/qxvc6
 
try this formula in cell G19 and fill it down make sure you press ctrl+shift+enter bcoz its array forumla and not just enter


INDEX(A:A,SMALL(IF($C$19:$C$28=F19,ROW($C$19:$C$28),""),ROW(A1)))


Note Press Ctrl+shift+enter
 
Thanks Ashish - that formula works if the bottom 3 values are the same, but if you change the data so the bottom 3 values are different you get a #num error.


Also, i still can't figure out how to let the user reference the region! very frustrating because it sounds easier than it is turning out to be!
 
OK, i've figured out how to take the Region into account.


Simply adding a helper column that populates depending on whether the region has been selected or not.


Then you can do the SMALL calculation on that column, still trying to figure out how to return the names of the engineers for the scores...
 
have u tried this one


=INDEX(A:A,SMALL(IF($C$19:$C$28=F19,ROW($C$19:$C$28),""),COUNTIF($F$19:F19,F19)))


ctrl + shift + enter
 
Thanks ashish, with the helper column in column D in place i've used your formula on the top example and it looks like it works!


INDEX(A:A,SMALL(IF($D$6:$D17=F8,ROW($D$6:$D17),""),COUNTIF($F$6:F8,F8)))
 
Hi JungleJme,


Please see this file:


http://dl.dropbox.com/u/60644346/SortingforValues.xlsx


The first formula that provides for the Index Value is :


Code: 
=INDEX($A$3:$A$12,MATCH(SMALL(IF($B$3:$B$12=$F$2,($D$3:$D$12)+ROW($D$3:$D$12)*0.0000001),ROW(A1)),($D$3:$D$12)+ROW($D$3:$D$12)*0.0000001,0),0) Ctrl+Shift+Enter


The Second formula that performs vlookup is:


=VLOOKUP(F4,$A$3:$D$12,3,FALSE)


Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top