Progress Chart

This is the second part of a four part article describing how one may build a “Progress Chart” in Excel. This will probably work in any spreadsheet with little alteration.
This chart will help one keep track of where one stands with regard to achieving one’s goal.

When trying to accomplish something I find it helps to have a visual indication of ones progress. A chart, or graph, showing ones target and ones progress is an ideal incentive to keep up, or to catch up, if you’re be getting behind.

A spreadsheet lends itself ideally to this end. In Part 1 I gave an outline of what we are trying to achieve and we started setting out the information at the top of the sheet.

 In part 1 we started setting out the input section of the spreadsheet. We also looked at formatting cells to suit our needs.

  

Now we shall calculate how much work we need to do each day.

 We need to study 351 pages (cell C3) in 78 days (cell H3). To find this we need to find out the number of working days. In cell G2 we find out the number of weeks that we have at our disposal – The end date minus the start date divided by 7 [+C4-C2/7]

If we set up cells K1-R3 as above and place a 1 under the days we wish to work and have the sum of cells K3 to Q3 in R4 [=SUM(K3:Q3)]. In this example we do not want to work on the weekends nor do we want to work on Wednesdays. This gives us a total of 4 days a week that we want to spend on this book.

To calculate the number of working days we have to complete the task, we multiply the number of weeks by the number of days per week that we will be working. The answer will be in cell G3 where we must enter the formula [+G2*R3]. Te result will be 13 (weeks) x 4 (working days per week) = 52 days in which to study our book.

To find our target we divide the number of pages we have to study by the number of days we have available(351/52). The formula in cell G4 will, therefore, be [+C3/G3]

 Next we will set up the diary:

 In cell C7 we will have the start date so we will copy that straight across from C2 by entering into the cell C7 [+C2]

We’ll increment our diary by one day at a time. The formula in cell C8, therefore, will be [+C7+1]. Copy this formula into all the cells below this as far as you wish to go by dragging the bottom right corner of the cell down. Remember to format the column from C7 onwards to show the date in the format of your choice.

 To get the day of the week in the column next to this we can enter the formula:-

 [=IF(D7=0,0,CHOOSE(WEEKDAY(C7,1),"Sun","Mon","Tue","Wed","Thur","Fri","Sat"))]

Once again, drag this down to fill as many rows as required.  I added =IF(D7=0,0,….) to the formula to avoid problems later.

  

We now want to enter, into our diary, the target of our reading for each day. That would be easy if we were reading every day, but we have elected to read only on certain days of the week. We must, therefore, condition our diary to add our daily target only to those day that we will be reading.

Rough working and elaborate conditioning I usually put out of sight to keep things looking neater and less confusing. I have selected columns well off the page for the next bit.

  In column AI I have reproduced the information from cells K1 to Q3 into a vertical column. It probably wasn’t necessary but it made it easier to be visible to me while I was doing the rest.

I typed in Sat, Sun, etc into cells AI2 to AI8, then in cells AJ2 to AJ8  type in the formulae [=if(K3=1,1,0)], substituting K3 with L3, M3, etc for the others. This will bring the information entered into cells K3 to Q3 into cells AJ2 to AJ8.

Bring, into column AK the info from column B with the formula [+B7] in cell AK7 then drag this down to fill the rest of the column. Then, to mark the days that you will be reading enter the following formula into column AL [=VLOOKUP(AK7,AI2:AJ8,2,FALSE)]. Once again drag this down to fill the rest of the column to the required row.

There will now be a 1 next to all the days that you will be reading.

EXPLANATION: In the last formula we have VLOOKUP. What we are doing here is looking up the occurrence of the word in cell AK7 ( Fri) in cell range AI2 to AJ8. When we find it we want the value in column 2 (being the second column in the range AI to AJ), which is a 1.

 

Progress Chart — Part 3

My next article will show you how to use the information in columns AK to AL to set your targets in your reading diary, taking into account we will not be reading every day of the week.

We will also look at CONDITIONAL FORMATTING.

comments powered by Disqus

Progress Chart

For those who want to keep track of their progress in attaining any goals that can be broken down into measurable units, this will take you through building your own Excel spreadsheet. The steps are quite easy to follow (I hope) and will explain a few of the Excel formulae and functions.

Progress Chart — Part 1

 This article will serve two purposes. The first is as stated below and the second will be to show you, as examples, different formulae and ‘tricks’ in Spreadsheet functions.

 This is designed for people, like me, who need an incentive to get things done. The idea is to plot a course to get from the beginning of a task to the end within a certain time frame. This applies to tasks which can be broken down into measurable units. The example I shall give will be for a student who has to read/study a subject from a textbook. This example will be one book, from one subject, and will take the form of a graph plotting the students progress through the book. The chart will compare the target number of pages to be read per day in order to complete the book by the given date with the number of pages actually read each day and will be done in an Excel Spreadsheet.

 This will be quite a long exposé so I shall deliver it in more-or-less ‘bite-sized’ chunks.

 The spreadsheet will comprise one sheet being devoted to each book of each subject being studied.

To start with we will need to know:

1. How many pages in the book we are studying.

2. The date we will be starting to study it.

3. The target date by which we want to have completed the study.

4. How many, and which, days of the week we wish to devote to the subject.

 Next we need to do some calculation:

1. How many days do we have in which to complete the task we have set ourselves?

2. Working on the number of days per week that we want to spend on the task, how many working days do we have to complete the task?

3. How much do we need to do each day to accomplish our goal?

 The top of the sheet will look like the example below.

 

So that we know where we are in our schedule it will be handy to be able to see today’s date at a glance. This will also be needed by our programme.

 To do this we write, in cell E1 “Today” so that we know what the contents of cell G1 is. In cell G1 we write =TODAY(). (“=“ tells the spreadsheet that you are entering a formula or spreadsheet function.) you will also need to format the cell (G1). Right click on the cell and choose “Format Cells”

 I have combined cells E1&F1 and G1&H1 just to give myself more space without making the cells from row 6 onwards too wide.

Then you select “Date” and then the date format hat you prefer.

 Similarly you can format all the other cells that will have dates in them. In fact you would format all the cells that you do not wish to show the information in the ‘default’ form. For example change the number of decimal places of ordinary numbers or change the ‘Time’ format etc.

 Once you have done this you can fill in the day you wish to start your studies, the number of pages you need to study and th target date you wish to complete the study

 Below is an example of what the final spreadsheet will look like with your progress table and chart showing your progress graphically.

 In my next article we will do some of the calcs where we will work out how much work we will need to do to keep within our schedule (A) and how to set up our diary of progress (B)

 We will also look at VLOOKUP.

 

comments powered by Disqus
Loading