Zehad Rian Jim
This tutorial will demonstrate different methods to calculate weight gain or loss in Excel. In the modern era, whether you are adjusting your own diet for better skin, getting fit for a healthier heart, or just want to look the best version of yourself, the first step is to make a plan and undoubtedly Excel is the best option for that. After going through this article, you will be able to create your own weight gain or loss tracker.
Download Practice Workbook
You can download the practice workbook from here.
5 Easy Methods to Calculate Weight Gain or Loss in Excel
To understand easily, we’ll use a sample dataset as an example in Excel. For instance, we have dates of the measured weight of a person in Column B and Measured Weight (KG) in Column C. Here, we’ll determine weight gain or loss in different methods with proper steps using this dataset.
1. Weight Loss or Gain Calculation Using Arithmetic Formula
Users use the arithmetic formula most commonly to determine profit/loss, weight gain/loss, etc. Subtracting the following weight and then dividing it by the initial weight results in a Weight Loss or Gain.
Steps:
- At first, click on the Home tab and select column D.
- After that, select Percentage format to display the resultant values in percentage form.
- Then, select the D5. Type NULL in it as it is the initial value.
- Afterward, select cell D6 and put the following formula in that cell:
=(C6-$C$5)/ABS($C$5)*-1
- Next, hit the ENTER and then drag the Fill Handle to show the weight loss or gain in percentage form.
In this formula, the ABS function passes the absolute resultant value of any number.
- Furthermore, click the Home tab.
- Then go to the Conditioning Formatting option and select Color Scales to see the result with color code and have a better view.
- After pressing Enter, you will get the following result.
- In addition, click the Home tab.
- Next, use Conditioning Formatting and select Icon Sets to see the result with directly weight gain or loss showing view.
- Finally, you will find the result in the following image.
Read More: How to Use Formula for Tracking Weight Loss in Excel (2 Examples)
2. Using MIN Function to Calculate Weight Loss or Gain in Excel
Sometimes, we need to find the overall weight gain or loss over months’ data. In that case, we can use the MIN Function in Excel. For that all we need to find the minimum weight within a range and deduct it from the initial weight value.
Steps:
- Firstly, activate the Percentage format similar to method 1.
- Secondly, put the following formula in the blank cell.
=(C5-MIN(C5:C10))/C5
- Lastly, press Enter to show the average weight gain or loss.
Read More: How to Use the CONVERT Function in Excel and creating a BMI Calculator template
3. Calculate Weight Loss or Gain with Excel LOOKUP Function
Similar to the MIN Function, we can use the LOOKUP function to calculate average weight gain or loss.
Steps:
- After selecting the Percentage format like method 1, apply the following formula in the blank cell.
=(C5 - LOOKUP(1,1/(C5:C10<>""),C5:C10))/C5
- Finally, hit the Enter button to get the average weight loss or gain.
🔎 How Does the Formula Work?
- (C5:C10): It represents the result_vector of Column C within the 5 to 10 cell range.
- LOOKUP(1,1/(C5:C10<>””): In this portion, value 1 in the LOOKUP function states the lookup_value, and 1/(C5:C10<>””) means the lookup_vector.
- (C5 – LOOKUP(1,1/(C5:C10<>””),C5:C10)): Substructing from initial value which is in cell C5.
- (C5 – LOOKUP(1,1/(C5:C10<>””),C5:C10))/C5: Divided by the cell C5.
Read More: Weight Loss Spreadsheet in Stones and Pounds (2 Useful Examples)
4. Utilize Combination of INDEX and COUNT Functions
We can easily use a combination of the INDEX function and the COUNT function to last row value in a given range. Using the combination we can easily calculate weight gain or loss in Excel.
Steps:
- Initially, use the following formula in the blank cell.
=(INDEX(C5:C10,COUNT(C5:C10),1)-C5)/C5
- Afterward, determine the result using the ENTER key.
🔎 How Does the Formula Work?
- COUNT(C5:C10),1: It represents row_num within the range of 5 to 10 cells and 1 is column_num.
- INDEX(C5:C10,COUNT(C5:C10),1): This INDEX part of the formula means (C5:C10) is an Array.
- (INDEX(C5:C10,COUNT(C5:C10),1)-C5)/C5: After dividing the INDEX portion with the initial value with cell 5.
5. Combine OFFSET and COUNT Functions in Excel
We can also use the OFFSET function to do the work similar to method 4 by assigning row and column numbers in Excel to get the latest weight value to subtract.
Steps:
- Firstly, apply the following formula in cell C6:
=(C5-OFFSET(C5,0,COUNT(C5:F5)-1))/$C$5
- In the final phase, we can determine the average weight loss or gain by pressing the Enter.
🔎 How Does the Formula Work?
- COUNT(C5:F5)-1: This part presents columns within the range C5 to F5.
- OFFSET(C5,0,COUNT(C5:F5)-1): The OFFSET function part in the formula collects the rightmost weight value to deduct it from the initial value.Cell C5 defines Value 0 defines rows.
- (C5-OFFSET(C5,0,COUNT(C5:F5)-1))/$C$5: Subtracting from initial value cell C5 and then dividing the whole function with cell C5.
Things to Remember
- The results of all the methods are shown in percentage form. So, the selection of the Percentage format is very important.
- Weight gain or loss by arithmetic formula (The Method 1) shows the change of values weekly with respect to the initial value.
- All other formulas show average weight gain or loss for a month or a few months of volume data.
Conclusion
Henceforth, follow the above-described methods. Thus, you can calculate the weight gain or loss in Excel and try to keep using them. Let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
