Automation help

Tech
1

Hello!

I need a little assistance automating beeminder data. I am trying to make a google sheet that gets data from Beeminder via IFTTT but I am having trouble figuring out how to make it work properly.

This is for a highly modified version of the 12 Week Year and we’re trying to make it nearly 100% automated and cheat-proof. I imagine some are familiar with the 12WY but if not, it’s essentially setting bigger goals that are broken down into smaller “tactics” (e.g. “lose 10 pounds” is a goal and “cardio 30 minutes a day” is a tactic) over the course of 12 weeks. The purpose of the sheet is to get the number of tactics completed/total intended for that tactic per week, but I am running into issues with the IFTTT applet.

I am trying to automate it so data is not counted for tactics that are time-based if the time is not met. For example, for the goal “do cardio for 30 minutes a day,” the data should not be counted if a person only does 20 minutes. I have accomplished this using =IF(logical_expression, value_if_true, value_if_false). So if someone does 20 minutes, the value for the cell with this equation will be 0; if they do 34 minutes, for example, it will be 1. Then I am using those 0s and 1s to calculate the total execution score by dividing the sum of all the 0s and 1s by the total number of 1s possible for the week. The problem that I am running into is that once the data from beeminder is sent to sheets, the equations that I have in my equation cells (that are separate from the cells I have the data sent to) gets shifted down one. For example, the function “=if(B15>20,1,0)” changes to “=if(B16>20,1,0)” once the data is sent to row 15. Is there any way to force sheets to keep it on a particular cell? Maybe Airtable would be a better option!

Thanks!!

1 Like
2

Hmm. One option might be to put your form entry into one sheet, and then
pull that data into another sheet with a simple formula. Another option
might prefixing the number with a $. That tells Sheets it’s an absolute
reference and not to increment it when the formula moves.

1 Like
3

Good suggestion! Unfortunately we need the data to update as it comes in. I can implement the equations after all the data comes in, but we’re trying to make it “live.” Where would I put the $?

Thank you for the help!

4

Ah.

Try this:

=if(B$15>20,1,0)

and see if it changes when you insert a new row.

1 Like
5

Didn’t work. Bummer! Thanks for the try!

6

Would having the equations be on a different topic sheet work? They can
refer to cells on other sheets, and maybe they wouldn’t change due to new
form entries.

1 Like
7

Is that like using the indirect function? If so, I tried that but couldn’t figure out how to make it work (I couldn’t even try it because I couldn’t get the equation right).

8

I figured out a solution! I added a function to sum all data for a particular date using this:

=SUMIF($A$1:$A$401,D1,$B$1:$B$401)

The dates are in column A and the data is in column B. My automated data starts on row 31, but I changed it to start counting at row 1 (and there is nothing in columns A and B until row 31. This forced it to count the automated data instead of moving the equation numbers down a row.

1 Like
9

Awesome!