Using date information to create a percentage complete column in Excel

[ejsdon]

On an Excel sheet tab I have a column with 108 rows of various tasks. The column next to this states the date in which each of these tasks is completed. Further along the sheet I have a column with the date on - or every date since the start of the project. Finally, I have a percentage complete column. What I would like to happen is as follows:

As I populate the date complete column, I would like the percentage column to recognise which date that happened on. I would then also like the percentage column to know how many of the 108 tasks this means have then been completed - thus giving me a percentage. So eventually i have a list of consecutive dates, with a column next to it updating the percentage complete of the full job as each day passes. I will manually input the date complete column.

I am happy to send an example sheet to anyone who can help.

[Pete_UK]

It would help if you attached a sample Excel workbook, and you can attach a file to one of your posts on this forum.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon does not work.

Pete

[ejsdon]

Thank You Pete. I think I have done it?

[Pete_UK]

Try this formula in cell J4:

=COUNTIFS(E:E,">0",E:E,"<="&H4)/(COUNTA(B:B)-1)

then copy down.

Hope this helps.

Pete

[ejsdon]

Ok, for anyone looking at the file ...
When the "Actual Date" column gets triggered (by me typing it manually on another tab and it appearing in column E) I want Column J "Actual Completion" to recognise this date and calculate the percentage of tasks that have now been complete. Column J would then also have to fill down from the last change in percentage. So I need column J to recognise both the date of occurrence and the percentage change.

[Pete_UK]

Did you see my Post #4?

You might need to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your Regional Settings.

Note that you have more than 108 tasks, which is why I use COUNTA.

Hope this helps.

Pete

[ejsdon]

Hi Pete,
That formula just gives me 0% all the way down the Actual Completion column.
Not sure if I have made my requirement clear? Or perhaps I should send you original sheet?
Thank You for your help.
ejsdon

[Pete_UK]

See attached file, where I have put that formula in J4 and copied down.

Hope this helps.

Pete

[ejsdon]

Thank You Pete - I have checked and made column name changes to the formula where required - works perfectly. Great help, thanks again.

[Pete_UK]

Glad it worked for you.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete