Friday, March 1, 2013

Use Excel to Track Word Count and Project Completion Date


I'm starting a new book and had to clear out the spreadsheet I used for the last one. When I did it made me think others might like to see how this works.
Here is a description of the spreadsheet followed by the formulas used. If you would like to see the actual spreadsheet I used, see the link below.

 
A
B
C
D
E
F
G
H
1
Book 4 Projected Word Count
 
 
 
 
Daily Goal
              500
 
2
 
 
 
 
 
 
 
 
3
 
 
Projected
Projected
Actual
Actual
 Actual -
Notes
4
DOW
Date
Words
Total
Words
Total
Projected
 
5
THU
2/28/2013
500
500
 250
250
(250)
 
6
FRI
3/1/2013
500
1,000
 700
950
(50)
 
 
F1
F2
F3
F4
F5
F6
F7
F8


Formulas
F1: =IF(WEEKDAY(B5) = 1, "SUN", IF(WEEKDAY(B5) = 2, "MON",  IF(WEEKDAY(B5) = 3,"TUE", IF(WEEKDAY(B5) = 4, "WED", IF(WEEKDAY(B5) = 5, "THU",  IF(WEEKDAY(B5) = 6,"FRI",  IF(WEEKDAY(B5) = 7,"SAT","X" )))))))

F2: Date you start writing.

F3: =$G$1 (Note: use absolute, signified by $ so you can copy the cell and not change the value.)

F4: For first instance (D5) use =C5. For D6 use D5+C6. For D7 use D6+C7, etc. Can be copied.

F5: Type in your total words for the day.

F6: =E5 first time, then =F5+E6, etc.

F7: =F5-D5

F8: Anything you want like excuses for not achieving your daily goal.

Once you fill in down to row 6, you can copy A6:H6 down until column D shows your word count goal. I usually set mine to 80,000 knowing I'll end up cutting 15-20,000 later. You can change your daily goal at any time and it will be reflected throughout. If you lower the daily goal, you will need to add more rows on the end to reach your total word count.

You can also adjust the daily goal to see how many words per day you would need to average to complete your total word count by a certain date.

A copy of the spreadsheet is available here: http://sidneywfrost.com/WordCount.xlsx.

If you would rather have a copy sent to you by email, let me know.

Hope you find this useful.

No comments:

Post a Comment