+ Reply to Thread
Results 1 to 8 of 8

need slight edit to current formulas

  1. #1
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    need slight edit to current formulas

    Hello,

    I started a thread a while back asking how to rank employees based on the amount of sales they have made (http://www.excelforum.com/excel-form...formances.html).

    Fotis1991, who is a genius by the way, seemed to have the answer. however, now im actually using the formula, ive realised that its not working perfectly.
    ive found that when two employees have the same total number of sales, the ranking lists just one of the employees twice. i was hoping it would list both and perhaps rank them by alphabetical/numerical order.

    so for example, in the attached workbook if you change cell AKJ3 on Our Raw Data to 20, you will see on sheet 3 that employee 1 is listed twice with no mention of employee 2.

    thanks if you can help
    Attached Files Attached Files
    Last edited by tlacloche; 10-29-2013 at 11:46 AM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,711

    Re: need slight edit to current formulas

    just a slight edit as request:
    In AKK2
    =IF(COUNTIF($A$2:$A2,$A2)>1,"",SUMIF($A2:$A1130,$A2,$AKJ2:$AKJ1130)+ROW()*10^-10)

  3. #3
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: need slight edit to current formulas

    thanks bebo. that works great.
    just one final thing. your edit causes larger numbers to be higher when there is a tie. can you make it so smaller numbers are listed higher.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,711

    Re: need slight edit to current formulas

    I am afraid that I could not catch you, can you give examples?

  5. #5
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: need slight edit to current formulas

    currently if employees 1, 2, and 3 had all the same number of sales, the ranking would be displayed as:
    3
    2
    1

    can you make it:
    1
    2
    3

    im guessing your edit to formula results in the larger number being ranked higher when there is a tie. i would like the lower number to be ranked higher

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,711

    Re: need slight edit to current formulas

    By this:
    +ROW()*10^-10
    I add a very small value to each row
    to reverse, change it to:
    -ROW()*10^-10

  7. #7
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: need slight edit to current formulas

    thank you bebo

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,711

    Re: need slight edit to current formulas

    Nice to hear it works, you are welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to edit the destination location of my current code
    By strud in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-14-2013, 05:12 AM
  2. [SOLVED] Slight edit needed with code..
    By strud in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2013, 03:50 AM
  3. [SOLVED] Slight edit needed - grouping x
    By strud in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-11-2013, 07:47 AM
  4. Edit An HTML Hyperlink Address To Current File Name
    By WJO in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-14-2012, 04:55 PM
  5. Edit to current macro help
    By Stuwil in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-05-2009, 06:49 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1