Screenshot (99).png

I decided to go down the nutrition path with this latest post. This post will run through how to create a very simple calorie and macronutrient calculator that you can use on yourself or your athletes, IF YOU KNOW WHAT YOU ARE DOING! As always, the data this chart spits out is not to be taken as gospel, but it will give you a relatively accurate estimate of some targets you can set depending on the individual stats you input.

The first bit you will need to create is a small table for the individuals data. This is the table on the left hand side of the picture at the top of the page. This individual data will form the basis of the BMR and TDEE equation that will help calculate individual calorie and macronutrient requirements.

Age, body weight (in kgs) and height will be input by the user manually. Then you need to create three drop down menus for activity, protein in (grams per kg of body weight) and fat intake (grams per kg of body weight).


To do this, simply click the cell next to your activity box (as seen below), then select the data tab and click on the data validation button (as highlighted above). I found that you don’t actually have to create a separate set of data to use as the source.You can simply add the data you require into the source box (with each data set separated by a comma). Highlighted below is the data set required for the activity drop down box.


The numbers are taken from the Harris Benedict Equation as in the table below. And are shown in table form on the far right hand side of my spreadsheet (See Top Picture)


Simply apply the same steps to input drop down menus for protein and fat intake, as the two following photos will show.



Once you have all the information you need to calculate your calorie intake, the next step is creating an equation that puts all this information together.  For this I like to use the Mifflin-St Jeor’ equation, which has been shown to be very reliable.

Men: (10 x weight) + (6.25 x height) – (5 x age) + 5

Women: (10 x weight) + (6.25 x height) – (5 x age) – 161

This equation only gives us basal metabolic rate, we then need to multiply this by total daily energy expenditure to get maintenance calorie levels, which can be calculate by relevant activity levels, using the Harris Benedict equation as in the table above.

So in my sheet in excel, the equation for males will look like this

=((B4*10) + (6.25*B5) – (5*B3)+(5)*B6


For females the equation will look like this:

=((B4*10) +(6.25*B5) – (5*B3) – (161))*B6


Once maintenance calories have been calculated, we now need to break these calories down into the three macronutrients; protein, fat and carbs. You can set protein intake anywhere for 1.2g to 3g per kg of bodyweight in my spreadsheet. I always set protein first when calculating individual macronutrient intake.

I could get into the protein recommendations, but I don’t want to get into the debate, as ideal recommendations will never be agreed upon!

The equation for the grams of protein and fat column are straightforward, just multiply the bodyweight (cell b4) by chosen protein (cell b7) and fat (cell B8) intake.


Then in the calories column just multiply the figure in the grams of protein cell by 4 to get the calorie intake for protein, multiply the figure in the fat cell by 9 to get calories for chosen fat intake.


Once protein and fat intake has been set, you can make up the remainder of your calories from carbohydrates. We will work the opposite way for this the calories for carbohydrates cell. Simply add up the calories from protein and fat together and take them away from the total calorie intake at maintenance levels, this will place the remaining calories in the carbohydrates column.

So in the calories column, across from carbohydrates type in: = (F6 – (F7+F8)

Then, in order to get the number of grams of carbohydrates, you simply divide the calories in the carbs cells by 4 to get your intake in grams.


Screenshot (98).png

For muscle gain and weight loss, all of the equations will remain the same for calculating protein, fat and carbs. The carbohydrate number will change because the calorie intake will change, depending whether or not you are in the muscle gain or fat loss column.

I don’t see the point in setting a 500 calorie increase or decrease for everyone, as a 500 calorie deficit could be massive deficit for a female, whose maintenance calories is 1800 calories. Likewise, a 500 calorie deficit for a 100 kg rugby player looking to lose fat, may not be enough either.

So for muscle gain I start a 10% increase in calories, and thus multiply maintenance calories by 1.1 to get my muscle gain calorie intake.

I like to start with a 15% decrease for fat loss, and thus multiply my maintenance calories by 0.85 to get my fat loss calories. These two percentages I feel are intelligent numbers to start with, as well as being relative to the individual.


You may need to alter protein and fat intakes for muscle gain and weight loss, especially if the original intakes used to calculate maintenance calories, are too low.

To finish off you can add some graphs or charts which give a nice illustration of where the calories calculated are partitioned, but probably more because it looks nice!

Screenshot (99)

As always I finish with a word of caution, this is a useful tool for those that already are nutrition coaches. Having a tool that spits out calorie and macronutrient targets does not give you the licence to become a nutrition coach!