In January I shared a post on using a story log to build my own collection of personal stories. I created a Google Form to enter the date when I entered the story and the story.
Google automatically sends the entry to a Google Sheets spreadsheet, on a worksheet titled “Form Responses 1.” Below is an entry from 1/10/2023. You can see that the “Timestamp” is different than the “Date Format” because sometimes I don’t enter a story until the next day.
Metrics
Capturing stories in some way is beneficial. In 2021 I used a small notebook and wrote down stories every day, for a total of 332 for the year. However, I also find it useful to track my progress over the year, and from year to year. Looking periodically at the stats keeps me motivated to keeping adding at least one story every day. The charts visualize the data.
My stats worksheet has 3 sections …
- Year by year comparisons. I initially started a story log in 2019, but stopped after 28 entries. In 2020 I only entered 4 stories. 2021 was my first successful year of writing a story almost daily. For 2022 I started and ended strong, with an extended lapse from February to September.
- Monthly for the current year. As the year progresses, I like to see how many stories I wrote each month. I particularly enjoy seeing months when I had more stories than days in the month.
- Goals. For 2023 I decided to compare my progress to 4 milestones, 50% and 100% of 2022 (132 stories) and 50% and 100% of 2021 (332 stories).
My goal for the stats worksheet is to have everything automated, so as soon as I submit a story using the form and it is added to the Form Responses worksheet, all of the stats update without any extra columns or manual calculations.
Workbook setup
My Story Log (Responses) workbook has 3 worksheets …
- Form Responses 1 is the data repository, as pictured above.
- Stats is the worksheet with my 3 sections of metrics.
- Lookup is a small crosswalk table that the stats worksheet uses to pull in the name of a month based on the number of the month (e.g., 3 = March).
Stats: Year by year
As noted above, my entries for 2021 are contained in a small notebook and are not included in the Form Responses data. Cells A2 to B5 are calculated from formulas, and I added Cells A6 and B6 manually. For the chart, I rearranged the data (C3 to D7) so that 2021 is correctly displayed in sequence with the other years on the chart.
YEAR. Cells A2 to A5 are generated using this logic: Look at the entire range of dates in the Form Responses sheet, extract unique years, and sort them chronologically.
The formula is …
=ArrayFormula(SORT(UNIQUE(YEAR(FILTER('Form Responses 1'!C2:C,ISNUMBER('Form Responses 1'!C2:C))))))
As much as I would like to take credit for the amazing formula, I found it using a search for “get unique years from list of dates.” This Stack Overflow article gave me the answers I needed.
COUNT OF STORIES BY YEAR. Cells B2 to B5 are based on this logic: Look at the entire range of dates in the Form Responses sheet and provide a count for the selected year.
The formula is …
=SUMPRODUCT(--(YEAR('Form Responses 1'!$C$2:C)=A2))
This ExtendOffice article provided the formula. Most spreadsheet functions are available and work the same in Microsoft Excel, Apple Numbers, and Google Sheets, so articles using one specific app likely apply to others.
Stats: Months for current year
CURRENT YEAR. The year value in Cell A19 is used in Cells C21 to C32 for counting the number of stories. On 2/28/2023 (the date of this post), the value returned is 2023. The year will automatically update when we reach 2024 and reset the month-by-month counts.
=YEAR(TODAY())
MONTH (M). Cells A21 to A32 use the same logic as the year in the year by year calculations, except using month instead of year: Look at the entire range of dates in the Form Responses sheet, extract unique months, and sort them chronologically.
=ArrayFormula(SORT(UNIQUE(MONTH(FILTER('Form Responses 1'!C2:C,ISNUMBER('Form Responses 1'!C2:C))))))
MONTH NAMES. Cells B21 to B32 reach out to the lookup worksheet discussed above. Logic: Look at each month number and find the corresponding month name.
=vlookup(A21,Lookup!A:B,2,false)
COUNT OF STORIES BY YEAR AND MONTH. Cells C21 to C32 are based on this logic: Look at the entire range of dates in the Form Responses sheet and provide a count for the selected month and the current year.
=SUMPRODUCT((MONTH('Form Responses 1'!$C$2:$C)=A21)*(YEAR('Form Responses 1'!$C$2:$C)=$A$19))
Stats: Goals
EXCEED PREVIOUS YEARS. Cells K9 and K10 are simple percentage calculations comparing progress in 2023 to 2020 and 2021 respectively. The goal value of 100% is included so that the chart will display all 3 metrics.
Exceed 2022 (Cell K9)
=B5/B4
Exceed 2021 (Cell K10)
=B5/B6
GOAL EVALUATION. Cells K14 to K17 use a conditional formula to display “met” when the corresponding number of story entries is achieved. For “2022 50%,” if the number of stories for 2023 equals or exceeds 66 (half of the total for 2022), then K14 should display “Met,” otherwise the the cell should show as empty.
=if(K9>0.499,"Met","")
The last formula is for ExpDate, short for “expected date.” Based on 1 story per day, the goal is expected to be met by taking the number of stories needed to reach the goal and adding it to 1-1-2023. For the goal of reaching the 2022 50% metric (66 stories), the logic is 1/1/2023 + 66, for a date of 3/8/2023.
=date(2023,1,1)+(B4/2)
In the screenshot above you can see that I entered “2-22-2023” in Cell M14 since K14 displays “met.” I’m ahead of schedule!
If you have a basic list of stories or other information, I encourage to figure out what types of metrics you’d like to track over time. Hopefully these examples have given you some ideas. A list of the functions I used is below.
Notes For Learning 