+ Reply to Thread
Results 1 to 7 of 7

Calculate expected percentage

  1. #1
    Registered User
    Join Date
    03-03-2015
    Location
    england
    MS-Off Ver
    2010
    Posts
    15

    Calculate expected percentage

    Hi,

    I have the following date of where my progress should be by these dates:

    Start: 01/08/2017:
    25% complete: 02/09/2017
    50% complete: 10/10/2017
    75% complete: 12/12/2017
    End: 31/12/2017

    I want to work out what % i should be on, based on todays date.

    As the amount of dates are not the same between each quarter i have been unable to work this out.

    Any help would be greatly appreciated.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Calculate expected percentage

    Should every day from October 10th 2017 to December 11th 2017, for example, return 50% complete?

  3. #3
    Registered User
    Join Date
    03-03-2015
    Location
    england
    MS-Off Ver
    2010
    Posts
    15

    Re: Calculate expected percentage

    No, for example on the 12th October it would show 52% (or whatever % would be correct)

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Calculate expected percentage

    That's tricky. Try this (see attachment)

    I created a lookup table which includes the completion %, the date, and the number of days in each range.

    I then used the following formula
    =SUM(LOOKUP(E2,B2:B6,A2:A6),(0.25*(E2-MAX(IF(B2:B6<=E2,B2:B6)))/LOOKUP(E2,B2:B5,C2:C5))) Ctrl Shift Enter
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-03-2015
    Location
    england
    MS-Off Ver
    2010
    Posts
    15

    Re: Calculate expected percentage

    Hi,

    Thanks for your help!

    When I choose today's date, the percentage is shown as -96%. Any ideas?

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,082

    Re: Calculate expected percentage

    When I put today's date (8/30/2017) in cell E2, cell F2 displays 22.66%, which seems reasonable.
    If you could upload a copy of the worksheet that shows your problem it might help someone figure out what is going on.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658

    Re: Calculate expected percentage

    Another way:

    A
    B
    C
    1
    Date
    Completion
    2
    1 Aug 2017
    0%
    3
    2 Sep 2017
    25%
    4
    10 Oct 2017
    50%
    5
    12 Dec 2017
    75%
    6
    31 Dec 2017
    100%
    7
    8
    31 Aug 2017
    23.44%
    B8: =PERCENTILE($B$2:$B$6, PERCENTRANK($A$2:$A$6, A8, 6))
    9
    30 Sep 2017
    43.42%
    10
    31 Oct 2017
    58.33%
    11
    30 Nov 2017
    70.24%
    12
    31 Dec 2017
    100.00%
    Entia non sunt multiplicanda sine necessitate

+ 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. Expected table is not in the expected format - ADODB Connection to Read Only Excel file
    By Roshan10043 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-05-2018, 10:54 AM
  2. Replies: 5
    Last Post: 07-06-2017, 12:00 PM
  3. Calculate expected time to finish task
    By oranb in forum Excel General
    Replies: 6
    Last Post: 03-08-2017, 12:47 PM
  4. How to calculate expected out of stock
    By sam94 in forum Excel General
    Replies: 7
    Last Post: 10-09-2014, 06:40 AM
  5. Macro to calculate Expected Sales
    By nitucse in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-16-2010, 12:27 PM
  6. [SOLVED] percentage:how do I calculate the percentage change
    By Knowledge001 in forum Excel General
    Replies: 3
    Last Post: 11-25-2005, 02:40 PM

Tags for this Thread

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