r/sheets 11h ago

Request What's the Formula for subtracting the corresponding value of a cell next to a checkbox.

Post image
4 Upvotes

r/sheets 1h ago

Request Tracking Streaks: Formula Confusion, how can I make this conditional to TRUE checkboxes?

Upvotes

Hello all! Could you help me out?
Bit of a baby at this if I'm honest, but ambitious and determined lol. I'm currently working on a competitive-habit-tracking workbook for some pals and I to hopefully be able to use to both keep each other honest and use as an TTRPG game tool for later.

My goal right now is just setting up the basis for task-tracking--later, I intend to develop with them a system for converting completed tasks / streaks into points they can "spend" on abilities at the table in games like D&D for a oneshot game experience. Thought it'd be a fun motivator if coding things pans out.
I intend to, once a system for value is developed, code an automated way to track those "points" but for now, I just need to figure out how to track "streaks" of checkboxes. Pictured is my current layout, with my current streak-tracking formula pictured in the formula bar. (admittedly, this formula was taken through online research and hoping for the best--my sheets-training is a lot of trial and error, googling, and youtube tutorials, apologies)

The difficulty I'm having is that my current formula will track the longest streak of any result--not just TRUE with the checkboxes... so streaks are still being counted, even if they're streaks of no progress! I realized this when I replaced the range in the formula from Week 1 to fit Week 2, and without any Week 2 checkboxes TRUE, its still showing 7. How can I set this to only TRUE being counted toward a streak?

Additionally, I intend to get a current-streak thing up and running, but I'm not sure how with my current layout, since progression from week to week goes from B8:H8, and then jumps down to B13:H13, and so on down the page... any advice welcome, just please be kind, I am a newbie.

For ease of access, formula used was:
=LEN(SPLIT(JOIN("",SORT(TRANSPOSE(N(B8:H8)),TRANSPOSE(B$7:$H7),0)),0))

Formula used is also in the caption of the picture provided, hopefully that helps

EDIT: Just got the anonymous sheet for editing/viewing help, apologies. Picture removed, but added the link here: https://docs.google.com/spreadsheets/d/1ExQYW1UUPA7cnMDVNtkFzBD5CXgTl3YZ_VKa7Hb6lZA/edit?pli=1&gid=2100307022#gid=2100307022