Formulas for story log tracking

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 Forms entry form

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.

Story entry as it appears in the Form Responses worksheet

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 …

3 worksheets in the Story Log workbook
  • 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).
Lookup data for month number and month

Stats: Year by year

Year by year stats and chart

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

Monthly counts 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

Goals and milestones

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.

Functions Used

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s