• 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.

Lookup Minimum value

R

Rajender

Member
Hi All,

Can anybody suggest me the formula to extract the name of person according to current week number.

my data range is from A3:BB7
Name Total Issues 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
A 2 1 2 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
B 3 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
C 0 0 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
D 0 1 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

I tried the below formula but not succesful.

="Top Performer of week "&WEEKNUM(TODAY(),2)&" is "&INDEX($A$3:$BB$7,MATCH(MIN($C$4:$BB$7),$C$4:$BB$7,0),MATCH(WEEKNUM(TODAY(),2),$C$3:$BB$3,0)-3)

Thanks in advance for your help.

Regards,
Rajender
 
@Rajender

Since you are going for the top performer why are you using MIN(). I had consider MAX in the below formula. Is this OK.

="Top Performer of week "&WEEKNUM(TODAY(),2)&" is "&INDEX($A$4:$A$7,MATCH(MAX(OFFSET(C3,1,WEEKNUM(TODAY(),2)-1,4,1)),OFFSET(C3,1,WEEKNUM(TODAY(),2)-1,4,1),0))

Just check and advise if any issue.

Regards,
 
Hi Somendra,

Thanks a lot for your assistance.
Its working fine. but I changed the Max with Min as in my case the person who did least mistakes will be the top performer.

Once again thanks a lot :)

Regards,
Rajender

Somendra Misra said:
@Rajender

Since you are going for the top performer why are you using MIN(). I had consider MAX in the below formula. Is this OK.

="Top Performer of week "&WEEKNUM(TODAY(),2)&" is "&INDEX($A$4:$A$7,MATCH(MAX(OFFSET(C3,1,WEEKNUM(TODAY(),2)-1,4,1)),OFFSET(C3,1,WEEKNUM(TODAY(),2)-1,4,1),0))

Just check and advise if any issue.

Regards,
Click to expand...
 
this is a good point... if the person with the LEAST number of mistakes is the 'top preformer', then how are you identifying a top preformer when there is more than 1 person with the same smallest number of mistakes? week4, for example, B & C & D all have 0 mistakes...
 
json said:
this is a good point... if the person with the LEAST number of mistakes is the 'top preformer', then how are you identifying a top preformer when there is more than 1 person with the same smallest number of mistakes? week4, for example, B & C & D all have 0 mistakes...
Click to expand...

oooh I forget this point.
If is it also possible that at least two person name reflect if they have equal score ?

Regards,
Rajender
 
@Rajender

Please have of look of attached file. The yellow cells have the formula. It will list all the top performer.

Regards,
 

Attachments

  • query.xlsx
    13.2 KB · Views: 7
Somendra Misra said:
@Rajender

Please have of look of attached file. The yellow cells have the formula. It will list all the top performer.

Regards,
Click to expand...
Hi Somender,

I need one more assistance from your side. If I also want to apply a formula to count how many times every individual got top performer of the week at year end then what will be function ?

Regards,
Rajender
 
@Rajender
Hi,

Please have a look of attached file. I had changed the way you were getting the top performer.
Now in your original template you can enter the final resolved issue week by week and the top performer get automatically updated in the grey cells. later you will get the count of top performance employee wise below that.

Just see if this is Ok or advise if any issue.

Regards,
 

Attachments

  • query(1).xlsx
    19.2 KB · Views: 5
Many thanks Somendra.

I applied the same format in my tracker.
Have a nice day to you.

If possible can you also help me on my another query in below given link

http://chandoo.org/forum/threads/au...-files-based-on-cell-values.14110/#post-83941

Thanks in advance.

Regards,
Rajender


Somendra Misra said:
@Rajender
Hi,

Please have a look of attached file. I had changed the way you were getting the top performer.
Now in your original template you can enter the final resolved issue week by week and the top performer get automatically updated in the grey cells. later you will get the count of top performance employee wise below that.

Just see if this is Ok or advise if any issue.

Regards,
Click to expand...
 
You must log in or register to reply here.
Back
Top