Monthly/weekly deficit tracking spreadsheets?

oat_bran

Does anyone know any good spreadsheets that allow tracking your deficit in weekly or monthly cycles by manually entering intake and TDEE each day?

I prefer tracking my actual deficit on a daily basis by using my fitbit TDEE, because my activity level varies greatly each day (+/-1000 calories), and that even without intentional exercise. My fitbit's estimates appear to be very accurate, but I abandoned trying to sync it with MFP, because I fail to do it correctly every time and it gives me too many calories to eat. I also feel like I need to be careful not to have a deficit that is too high on each particular day or I will find myself very hungry for the next few days.

I want to try "banking" calories and have a clearer idea about my deficit in weekly and even better monthly cycles. Somebody gave me this idea to manage overeating days when I'm increasingly hungry due to hormonal fluctuations. It would be cool to have a spreadsheet that does calculations for you.

EDIT:
to give you an idea of what I'm looking for, here's a table I'm currently using to keep my deficit weekly deficit. It would be cool to have a spreadsheet like this but that would do calculations for you and track calorie allowances on a weekly or daily basis.

WholeFoods4Lyfe

Honestly, MFP works great for me, and I love that I can export my data.

What about the syncing were you having issues with? Mine is set up to sync automatically and I have zero issues.

FWIW you don't have to eat back all of the exercise calories if you don't want, I generally ignore them when MFP adds them to my daily total.

kdcatt

There are a couple of good ones I found on Reddit. One that specifically compares Fitbit TDEE to another calculation for TDEE. I use both, so track 3 ways and they are all within 100 calories of each other. That being said, I still eat less than it tells me to because I can't wrap my head around the thought that I can eat that much, but trying to inch up to that goal and eat more. If you use them, read through the comments to get a better idea of how to use the sheets.

https://www.reddit.com/r/Fitness/comments/4ilksm/updated_adaptive_tdee_tracking_sheet/
https://www.reddit.com/r/xxfitness/comments/4mf2iv/psa_fitbit_tdee_inaccuracy_isnt_always/

oat_bran

michelle172415 wrote: »

Honestly, MFP works great for me, and I love that I can export my data.

What about the syncing were you having issues with? Mine is set up to sync automatically and I have zero issues.

FWIW you don't have to eat back all of the exercise calories if you don't want, I generally ignore them when MFP adds them to my daily total.

Well, like I said my activity level varies too much every day. There are days where I burn 2700 and other days I only burn 1850 (the last week in my table is not a perfect example as I was very active every day). Most of this activity is NEAT, it's not intentional exercise, I just have 2 very active jobs and walk a lot. So what kind of activity level do I set? Some people have advised me to set my MFP to sedentary and let fitbit count the "exercise". Except that from what I understand, fitbit doesn't really distinguish between exercise activity and NEAT, it counts TDEE. So the number MFP gives me is always somewhat different from the number I get if I subtract my desired deficit from my TDEE. Everybody offered me a different solution for this when I brought up this problem, but nothing really worked. Maybe I'm just not getting how MFP calculates exercise calories and activity.

I don't really get the idea of distinguishing between exercise calories and other calories because I do a lot of NEAT and my activity varies so much. How does my body know if I burned those 400 calories because I was running up and down the stairs carrying heavy laundry baskets and cleaning the house or walking from class to class on campus or running on a treadmill? Also, like I said I need to be careful with my actual deficit to avoid getting too hungry so if I burned a lot of calories on a particular day I need to calculate how much I should eat using my actual TDEE.

Deviette

I'm confused, are you saying that your current spreadsheet doesn't have any formulas in it and you're doing all the calculations by hand and then inputting them?

oat_bran

deviette wrote: »

I'm confused, are you saying that your current spreadsheet doesn't have any formulas in it and you're doing all the calculations by hand and then inputting them?

Yes, exactly! I calculate everything myself. I guess I can make a similar spreadsheet on Excel, but I'm ashamed to admit that I am not very good at it. So I thought that it would be easier to find a spreadsheet that already has all the formulas in it. And I was wondering, if there's a spreadsheet that I can enter for example monthly deficit goals, which would allow to bank calories and use them later in a month.

amusedmonkey

It's easy to make your own. The most basic one would be to add 3 columns: calories consumed, calories burned, calories left. Having 30 rows under these headers will allow you to log 30 days.

On the side in a separate cell, you would also have your target deficit for the sake of calculations.
- Calories consumed: you enter them manually
- Calories burned: you enter them manually
- Calories left is a calculation from the two pervious columns:
[adjacent calories burned cell] - [adjacent calories consumed cell] - [desired deficit cell]
- At the bottom of your "calories left" column, just do a sum function for all the entries in the column and that will give you your total bank.

snemberton

michelle172415 wrote: »

Honestly, MFP works great for me, and I love that I can export my data.

What about the syncing were you having issues with? Mine is set up to sync automatically and I have zero issues.

FWIW you don't have to eat back all of the exercise calories if you don't want, I generally ignore them when MFP adds them to my daily total.

I opted for a spreadsheet to track closer to what my Apple Watch has for my daily calorie burn. Apple Watch doesn't sync steps properly with MFP, plus I feel my active plus resting calorie total is closer to accurate than the settings of sedentary, lightly active, etc. I tracked my daily totals for a few weeks and decided 2000 calories of food intake each day, with my TDEE calculated average of 2700 (from my watch) was a good deficit. MFP does a great job with being able to watch my intake each day, but it isn't as functional as I'd like with regard to other factors. I've been tracking this way for 3 weeks now and my expected weight loss would be 3.79 lbs and my actual loss is 3.31, so less than half a pound. If I was more strict with my food logging, it would probably be even closer. But honestly, unless I hit a time when I'm not averaging 1 pound a week lost (or down the road when I get closer to my goal .5lb), then I'll tighten up logging food first, then maybe consider the accuracy is off. As of now though, it's close enough to make me happy.

I don't know that the default settings in MFP can compensate well enough when I have an active, but non-intentional exercise day, especially with the poor sync with the Apple Watch and steps right now. My combo method shows me that I may have been even more active and can splurge on a piece of pie or two glasses of wine with dinner and not sacrifice my deficit.

jjpptt2

So, with MFP you're not sure what to set as your daily goal, right? That doesn't go away if you start using a spreadsheet. A weekly goal is simply the total of 7 daily goals, so I'm not sure a spreadsheet will solve that particular problem for you.

How long have you been using your fitbit? If you have a couple week's (minimum) worth of data, see what your average daily and/or weekly numbers are. That should give you a sense as to what your average daily goal should be set to in MFP.

Once you do that, the rest of it is pretty standard. MFP, by default, looks at daily figure, but if you go a little deeper (at least on the andriod app this works) you can see your numbers for the week.



If that doesn't work for you, or you just prefer the spreadsheet option, it's pretty easy to do as nothing you're talking about is especially complex. The biggest part will be deciding what data you want to track and how you want them totaled/averaged/etc. I (and probably others), would be happy to help with that... and a quick google search will yield lots of how-tos on specific excel formulas.

amusedmonkey

To add a formula, here is an easy way to do it that doesn't require a lot of knowledge:
1. Click the first cell of the "calories left" column (not the title one, the blank one right under it)
2. Type "=" (the equal sign, without quotes)
3. Click the "calories burned" cell that's in the same raw
4. Type "-" (the minus sign without quites)
5. Click the "calories consumed" cell that's in the same raw
6. Type "-" (the minus sign without quites)
7. Click the cell that has your desired deficit number, wherever it is (not part of these columns)
8. Click enter
9. Now select that cell again and put the mouse at the bottom right corner of the cell you're working with and watch the curser turn into a +. When it does, drag it all the way down until you reach your last day.

For the sum:
1. type
=sum(
2. then by dragging, select all of the cells you want to add up under "calories left"
3. close the parenthesis.

It should look something like this:
=sum(C2:C31)

Here are a couple of videos that might help show you what I just explained:
https://www.youtube.com/watch?v=bmA1FPFplRs
https://www.youtube.com/watch?v=UgeEeEESJxE

maybe1pe

I use one that was set up already by someone else.

I googled: tdee tracking spreadsheet
There's a reddit thread that came up as one of the first options and the post has a few links to google spreadsheets you can download to use.

https://drive.google.com/file/d/0B8EbfzFB0mBrMGJ6V2N5QWNfeTg/view

specifically that's the one I use. I downloaded and saved to my computer and entered the information. The formulas were already set up.

It does a little more than yours. If you enter your weights and calories you ate then it will calculate and average your actual TDEE over time. This spreadsheet is actually how I found out my Fitbit was low on estimating my calories.

oat_bran

amusedmonkey wrote: »

It's easy to make your own. The most basic one would be to add 3 columns: calories consumed, calories burned, calories left. Having 30 rows under these headers will allow you to log 30 days.

On the side in a separate cell, you would also have your target deficit for the sake of calculations.
- Calories consumed: you enter them manually
- Calories burned: you enter them manually
- Calories left is a calculation from the two pervious columns:
[adjacent calories burned cell] - [adjacent calories consumed cell] - [desired deficit cell]
- At the bottom of your "calories left" column, just do a sum function for all the entries in the column and that will give you your total bank.

amusedmonkey wrote: »

To add a formula, here is an easy way to do it that doesn't require a lot of knowledge:
1. Click the first cell of the "calories left" column (not the title one, the blank one right under it)
2. Type "=" (the equal sign, without quotes)
3. Click the "calories burned" cell that's in the same raw
4. Type "-" (the minus sign without quites)
5. Click the "calories consumed" cell that's in the same raw
6. Type "-" (the minus sign without quites)
7. Click the cell that has your desired deficit number, wherever it is (not part of these columns)
8. Click enter
9. Now select that cell again and put the mouse at the bottom right corner of the cell you're working with and watch the curser turn into a +. When it does, drag it all the way down until you reach your last day.

For the sum:
1. type
=sum(
2. then by dragging, select all of the cells you want to add up under "calories left"
3. close the parenthesis.

It should look something like this:
=sum(C2:C31)

Here are a couple of videos that might help show you what I just explained:
https://www.youtube.com/watch?v=bmA1FPFplRs
https://www.youtube.com/watch?v=UgeEeEESJxE

Thank you soooo much for taking your time to explain this!! This is so helpful! I'm going to to do one today!!

Deviette

I made a formula version of your one:

https://drive.google.com/open?id=1T_CM6CySgwEGDoeIX7btbCTFOsyxsTNi

I wanted to work out how to do months, but it's going to need a little more planning (if only a month was exactly 4 weeks long, would make life easier)

oat_bran

kdcatt wrote: »

There are a couple of good ones I found on Reddit. One that specifically compares Fitbit TDEE to another calculation for TDEE. I use both, so track 3 ways and they are all within 100 calories of each other. That being said, I still eat less than it tells me to because I can't wrap my head around the thought that I can eat that much, but trying to inch up to that goal and eat more. If you use them, read through the comments to get a better idea of how to use the sheets.

https://www.reddit.com/r/Fitness/comments/4ilksm/updated_adaptive_tdee_tracking_sheet/
https://www.reddit.com/r/xxfitness/comments/4mf2iv/psa_fitbit_tdee_inaccuracy_isnt_always/

Thank you! The shraedsheet in the first link appears to have been deleted but the second one looks great for comparing fitbit TDEE estimates and actual burn!

amusedmonkey

oat_bran wrote: »

amusedmonkey wrote: »

It's easy to make your own. The most basic one would be to add 3 columns: calories consumed, calories burned, calories left. Having 30 rows under these headers will allow you to log 30 days.

On the side in a separate cell, you would also have your target deficit for the sake of calculations.
- Calories consumed: you enter them manually
- Calories burned: you enter them manually
- Calories left is a calculation from the two pervious columns:
[adjacent calories burned cell] - [adjacent calories consumed cell] - [desired deficit cell]
- At the bottom of your "calories left" column, just do a sum function for all the entries in the column and that will give you your total bank.

amusedmonkey wrote: »

To add a formula, here is an easy way to do it that doesn't require a lot of knowledge:
1. Click the first cell of the "calories left" column (not the title one, the blank one right under it)
2. Type "=" (the equal sign, without quotes)
3. Click the "calories burned" cell that's in the same raw
4. Type "-" (the minus sign without quites)
5. Click the "calories consumed" cell that's in the same raw
6. Type "-" (the minus sign without quites)
7. Click the cell that has your desired deficit number, wherever it is (not part of these columns)
8. Click enter
9. Now select that cell again and put the mouse at the bottom right corner of the cell you're working with and watch the curser turn into a +. When it does, drag it all the way down until you reach your last day.

For the sum:
1. type
=sum(
2. then by dragging, select all of the cells you want to add up under "calories left"
3. close the parenthesis.

It should look something like this:
=sum(C2:C31)

Here are a couple of videos that might help show you what I just explained:
https://www.youtube.com/watch?v=bmA1FPFplRs
https://www.youtube.com/watch?v=UgeEeEESJxE

Thank you soooo much for taking your time to explain this!! This is so helpful! I'm going to to do one today!!

Forgot an important edit. Dragging to copy formula will mess up how it subtracts your deficit if you don't mark it as non-changing. To solve that add a $ sign before the cell letter another before cell number in the formula before dragging.
Example: if you have your desired deficit in the cell E2 and your calories left formula looks something like this

=B2-A2-E2

Add $ signs to E2 before dragging to prevent it from changing. It would look something similar to this

=B2-A2-$E$2

Then drag away

Don't be intimidated, you don't have to understand what that does, all you need to do is just add the dollar signs.

oat_bran

maybe1pe wrote: »

I use one that was set up already by someone else.

I googled: tdee tracking spreadsheet
There's a reddit thread that came up as one of the first options and the post has a few links to google spreadsheets you can download to use.

https://drive.google.com/file/d/0B8EbfzFB0mBrMGJ6V2N5QWNfeTg/view

specifically that's the one I use. I downloaded and saved to my computer and entered the information. The formulas were already set up.

It does a little more than yours. If you enter your weights and calories you ate then it will calculate and average your actual TDEE over time. This spreadsheet is actually how I found out my Fitbit was low on estimating my calories.

This is not exactly what I was talking about because I prefer to use my daily TDEE estimates to track daily AND weekly/monthly deficit. But the spreadsheet in your link could be pretty useful, too! It would be great to know how accurate fitbit is. From my previous losses it seems quite accurate but it would be interesting to know the exact discrepancy if there's one.

oat_bran

amusedmonkey wrote: »

Forgot an important edit. Dragging to copy formula will mess up how it subtracts your deficit if you don't mark it as non-changing. To solve that add a $ sign before the cell letter another before cell number in the formula before dragging.
Example: if you have your desired deficit in the cell E2 and your calories left formula looks something like this

=B2-A2-E2

Add $ signs to E2 before dragging to prevent it from changing. It would look something similar to this

=B2-A2-$E$2

Then drag away

Don't be intimidated, you don't have to understand what that does, all you need to do is just add the dollar signs.

Hey, I just wanted to thank you again for taking your time to explain everything. I used your instructions as guide lines and also watched a few tutorials on youtube and managed to make a spreadsheet EXACTLY like I wanted!

I made a screenshoot where I entered numbers already as an example just to brag a little because I'm really proud of myself



I have a column called calorie bank where I track how my actual deficit deviates from my target deficit and this allows me to have a clearer idea by how many calories I can overeat on any given day without having to eat below my target deficit for the rest of the month.

I also made a column that shows how many more calories I need to burn to reach my target monthly deficit and how many calories I have left to burn until I'm at my goal weight. The last column shows my total predicted loss in pounds on a daily basis.

And the header should allow to compare my fitbit TDEE estimates to my true TDEE. And I can also do comparisons on a daily or weekly basis using another spreadsheet someone recommended in this thread, where I enter calories eaten and weight everyday. From my previous experience my fitbit's estimate appear fairly accurate but it would be great to know how much exactly.

I'm super excited to start entering date and use the baking feature ! Thanks again !

amusedmonkey

This looks amazing! Best of luck and glad to have been of help. My first excel sheet with functions didn't look anywhere like yours. I remember it was a single average function for the average time I spent on studying each subject back in college, and I was so proud of it haha. Your first excel sheet puts my first to shame.

stanmann571

amusedmonkey wrote: »

This looks amazing! Best of luck and glad to have been of help. My first excel sheet with functions didn't look anywhere like yours. I remember it was a single average function for the average time I spent on studying each subject back in college, and I was so proud of it haha. Your first excel sheet puts my first to shame.

Probably because he had a better instructor than you did

oat_bran

amusedmonkey wrote: »

This looks amazing! Best of luck and glad to have been of help. My first excel sheet with functions didn't look anywhere like yours. I remember it was a single average function for the average time I spent on studying each subject back in college, and I was so proud of it haha. Your first excel sheet puts my first to shame.

But I spent an entire day on this and had lots of video tutorials, spreadsheets made by others and your instructions to guide me! Excel is a pretty important skill in any case and I'm happy that you inspired me to try to learn it!

Silentpadna

oat_bran wrote: »

amusedmonkey wrote: »

This looks amazing! Best of luck and glad to have been of help. My first excel sheet with functions didn't look anywhere like yours. I remember it was a single average function for the average time I spent on studying each subject back in college, and I was so proud of it haha. Your first excel sheet puts my first to shame.

But I spent an entire day on this and had lots of video tutorials, spreadsheets made by others and your instructions to guide me! Excel is a pretty important skill in any case and I'm happy that you inspired me to try to learn it!

It's always worthwhile to take a little time to learn something that can make your life easier. You'll be a 'guru' in no time.

Seriously though, you'd be amazed at how powerful of a tool programs like Excel (and others) can be. If you can think of what it might be able to do, chances are a programmer has already thought the same thing. If you search Help and tutorials, it's not hard to find.....like you've already done.