Post number 4 of “The Practical Google Sheets Guide”
These blog posts are part of research I’m doing for a potential new book, hopefully allowing me to further my love for spreadsheets. Theses posts are hoping to provide a practical cookbook of examples to the reader and allow me to learn what people are interested in and how to approach different subjects working with Google Sheets.
TLDR: This post will create a user interface to allow users to enter in there basic health data and get a Total Daily Energy Expenditure calculated for them. The complete Google Sheet can be copied directly from the link: https://docs.google.com/spreadsheets/d/15wbWqcyr_biIqeZpkefccOojYqMningxnXw5-fXLJuQ/copy Introduction
I’m not a massive fan of counting calories, especially if someone is trying to lose weight, but I do think it is interesting to make sure you are getting enough food for the work and activities your are doing. In this post we will show you how to set up a basic dashboard that will allow a user to enter in their height, weight, age and the amount of activity they generally do each day, and will output the users Basel Metabolic Rate(BMR), and their Total Daily Energy Expenditure(TDEE). A persons BMR is the amount of calories someone needs simply to function without any activity, this would be if the person was asleep, sedentary or in a coma for the entire day. This is a really simplified explanation, but this post is more directed to the set up and calculations of the spreadsheet. Someone’s TDEE, is someones BMR, and includes their usual daily activity and gives them a good idea of the amount of food they should be taking in to fuel their lives.
What You Need To Know To Get This Done?
For todays post we will do a few different things to make our user interface looks a little nicer than a normal sheet. We will set up two seperate sheets to allow the user to choose between metrics values and imperial values. We will add in a simple drop down menu to allow the user to choose between the different activity levels and to calculate our BMR and TDEE, we will add in some basic IF functions to perform our calculations for us.
When it comes to IF functions, we will also extend our IF functions to only display data when the user provides input details. Otherwise the cell will be blank. These IF functions will also get a little complex when calculating our BMR and TDEE, but we will walk you through everything so you understand what is going on along the way.
All the functions mentioned can be found at the following reference page, in case you need to double check how the function works:
https://hive.blog/hive-163521/@strava2hive/my-google-sheets-function-reference
Setting Up Our TDEE User Interface
1.We can start by setting up a basic interface to allow our users to enter in their details. The image below, gives us a bit of a break down.
- Leave column A for now as it will look a little nicer to leave this column blank. But add in the word Metric in cell B1 and Imperial in C1. We will then use these values later to set up a “fake” menu at the top of the screen.
- Start by setting up the Gender values, with a dropdown list in cell C3, with the two values of either Male or Female. If you have not done this before, click on C3 and select “Drop-Down” from the Insert menu. You will be able to add extra values on the left hand side of the screen. When you are happy with the entries, click on Done.
- Add an entry cell for Age, Weight and Height, as we have below. We will set this first sheet up as Metric and work on the Imperial fields later.
- Lastly, add another Drop-Down for Activity level. When the pace is first displayed, the drop-down should display “-”, so this will be the first value you add. Also add a further 5 items in the drop-down as:
- "Sedentary (Office Job)"
- "Light Exercise (1-2 days/week)"
- "Moderate Exercise (3-5 days/week)"
- "High Exercise (6-7 days/week)"
- "Athlete (2x per day)")
2.We can now look to create the top menu. Basically, all it will be doing is linking each of the values from a sheet specific for metric, to a sheet specific to imperial.
- Start by naming your current sheet by clicking on the arrow in the sheet tab, and select Rename. Change the name from Sheet1 to Metric.
- Now duplicate the Metric sheet, again click on the arrow again and select “Duplicate”. This will create a copy with the name on the bottom tab as “Copy of Metric”. Again, select the arrow in the tab, and rename this new sheet to “Imperial”
- While you are in this sheet, to to the top of the screen where the browsers has its URL for the sheet. At the end of a long URL, you will see a hash symbol(#) with a gid value, and make a copy of it. For me, that value is #gid=514083049 and yours will be different.
- Go back to the Metric sheet, as we have in the image below, highlight the Imperial heading at the top of the screen in cell C1. Go to the Insert menu and select “Link”. You will then be presented with a similar image to the one below.
- Add the #gid number you copied earlier for the Imperial sheet, and select “Apply”. When you click on this link now, it will send you to the Imperial sheet that you set up.
- Now create the same link on the Metric value in B1, this time linking the Imperial sheet back to the Metric sheet.
As you can see in the image below. The Imperial sheet will look a little different, as we have different values for users to enter in. Weight will be in pounds or “lb”, we also need a seperate Height value for both feet(ft) and inches(in).
3.We can now set up the results area on the left of the sheet. I usually use the G and H columns, merging them together as we have below.
- Row 3, will have the title of “Your Results”, where Rows 4 and 5 can be merged together and will display our Total Daily Energy Expenditure value. I have my example set as a Number value, with the font size as 25 and Bold.
- Row 6 will be another title of “Calories Per Day”.
- Add the same titles in as we have for “Basel Metabolic Rate”, and “Calories Per Day” as we have in the image below:
4.We can now add the formulas to calculate the BMR and the TDEE, starting with out metric values. Both of these will be large nested IF statements. BMR is calculated using the the
Mifflin-St Jeor equation, which is a widely used method for estimating basel metabolic rate
- In cell G9, we will add the formula to calculate the BMR.
=IF(C11="-", "",IF(C3="Male", (10*C7)+(6.25*C9)-(5*C5)+5, (10*C7)+(6.25*C9)-(5*C5)-161))
This formula starts by looking at cell C11 to see if there is an entry, if it is set to “-”, the value will be left as blank. If not, if will then start the calculations, then checking the value in C3, if it is “Male” use the Mifflin-St Jeor equation for men, and if not, use the equation for women. - In cell G4, we can now add in the formula for TDEE. This uses the value in activity level drop-down to assign a value that your BMR is then multiplied by. The formula is:
=IF(C11="Sedentary (Ofice Job)", G9*1.2, IF(C11="Light Exercise (1-2 days/week)", G9*1.375, IF(C11="Moderate Exercise (3-5 days/week)", G9*1.55, IF(C11="High Exercise (6-7 days/week)", G9*1.725, IF(C11="Athlete (2x per day)", G9*1.9, "")))))
6.Our Imperial sheet will be a little different. Switch over to the sheet and we will add in some extra calculations to convert our Imperial height and weight values into metric, to then perform our BMR and TDEE calculations.
- As in our image below, move to cell C14, and we will convert our weight value from pounds to kilograms, by multiplying the value in C7 by 0.453592. So in the cell C14, our formula will be:
=(0.453592*C7) - We can do the same with our height calculations, changing our feet values to inches and adding it to our inches value. This then gets multiplied by 2.54 to get our height in centimetres. So the formula in C16 will be:
=((12*C9) + E9) * 2.54 - Our BMR formula in G9 will now use these values in C14 and C16 instead of the imperial values. We won’t go into detail, but the new formula will look like the one below:
=IF(C11="-", "",IF(C3="Male", (10*C14)+(6.25*C16)-(5*C5)+5, (10*C14)+(6.25*C16)-(5*C5)-161)) - Our formula to calculate our TDEE will now be exactly the same, and you can take if from our previous calculations in the Metric sheet.
- Once everything is working well, you can now change the font colour of the cells in C14 and C16 to be white and not be shown to the user.
6.Finally, we can add a nice message at the bottom of the screen giving a brief explanation on what the results are and how the results were calculated. We can use the IF function to only display if we have data to present to the user, and the CONCATENATE function to join some text together and include the value calculated as the TDEE for the user.
- In the image below, I have used cell E13 and merged it through the range E13:H16. I have merged all the cells, set the text as “left” formatted, placed the text at the Top and enabled “text wrapping”.
- I have then added the following formula:
=IF($C$11="-", "", CONCATENATE("The details on this page are an estimate based on the values you have entered. We have calculated that you need “, G4, “ calories per day, based on the Mifflin-St Jeor Formula, which is widely know to be the most accurate."))
If the value in C11 has not data selected, the cell will be blank, other wise the text will be presented to the user, explaining how the values were achieved and including the values from G4, which is the calculated TDEE.
The Imperial sheet should also mirror the Metric sheet in most aspects except for the values entered into the form. There has been some good work done to make a nice looking and function form here. We have set up basic formatting and created two seperate sheets for both Imperial and Metric. We have a nice interface to allow users to enter their values as both text and via drop-down menus. We have added in our calculation to calculate the users BMR and TDEE and included some large IF functions to present the data in a nice way to our user.
About The Author
