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:-
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.