# Calculate expected percentage

1. ## 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. ## Re: Calculate expected percentage

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

3. ## Re: Calculate expected percentage

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

4. ## 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

5. ## Re: Calculate expected percentage

Hi,

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

6. ## 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.

7. ## 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%

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