+ Reply to Thread
Results 1 to 6 of 6

Ranking issue.

Hybrid View

  1. #1
    Registered User
    Join Date
    07-30-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    12

    Ranking issue.

    I am trying to create a workbook which will monitor the trailers off loading at our depot.

    Column D Shows the arrival date.
    Column E Shows the arrival time.
    Column F Will show the queue position. (The off load order 1,2,3,4 etc. This varies as other trailers are off loaded).
    Column G Shows the date / time when the trailer completed off load.

    In cell F3 I entered the following =IF(G3>1," ",(IF(AND(E3=0,G3=0)," ",RANK(E3,$E$2:$E$50,1))))
    The result is 2.
    A similar function in F5 gives a result of 3.
    I had hoped the results would have been 1 (F3) and 2 (F5).
    Cell F2 is blank because G2 contains data. If data was removed this would show 1.

    Is it possible to alter the relative queue positions (Column F) using "RANK" or maybe another function?

    Any assistance would be greatly appreciated.
    Attached Files Attached Files

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

    Re: Ranking issue.

    Could you pls upload a spreadsheet instead a word doc?
    Quang PT

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

    Re: Ranking issue.

    Cause of with .doc I dont know what is the format but if D column is real date formated as dd.mmm.yy, try in F1:

    =IF(OR(G1<>"",D1+E1=0),"",SUMPRODUCT(--(IF($D$1:$D$4+$E$1:$E$4<>0,IF($G$1:$G$4="",$D$1:$D$4+$E$1:$E$4,""),"")<=(D1+E1))))
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Drag down.

  4. #4
    Registered User
    Join Date
    07-30-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Ranking issue.

    Thanks for your quick response.
    Column D is formatted dd.mm.yy
    I changed to dd.mmm.yy and inputted your array formula in cell F2. Unfortunately received #Value!
    Sorry but I am not familiar with "array formulas"
    Could you have another look?
    Attached Files Attached Files

  5. #5
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 365
    Posts
    2,406

    Re: Ranking issue.

    Put on F2 and copie down:

    =IF(G2>1," ",(IF(AND(E2=0,G2=0)," ",COUNTIF($E$2:$E$5,"<"&E2))))
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-30-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Ranking issue.

    That's done it.
    Thanks for your help.

+ 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. Ranking Issue
    By gamefreak in forum Excel General
    Replies: 1
    Last Post: 12-02-2014, 01:00 PM
  2. [SOLVED] Help with #value! issue in ranking formula
    By BitterGreg in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-14-2014, 04:13 PM
  3. Ranking (weighted) issue
    By jw01 in forum Excel General
    Replies: 4
    Last Post: 09-07-2011, 08:10 PM
  4. Ranking Issue
    By IdRatherBeCoding in forum Excel General
    Replies: 2
    Last Post: 05-23-2011, 11:28 AM
  5. Ranking Issue
    By acg in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-11-2007, 05:28 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