Users use Excel as a tool to track certain things such as Costing, Weight Loss, etc. Using a worksheet to calculate weight loss percentage in Excel is common nowadays. Typical Arithmetic Formula, other Excel Functions to fetch the most recent or minimum value to calculate weight loss percentage in Excel.
Let’s say we have weight data intervals every 15 consecutive dates. And we want to calculate the weight loss percentage for each consecutive date or overall period of time.
In this article, we demonstrate cases to bring desired value (that can be minimum or lookup) to calculate weight loss percentage in Excel.
How to Calculate Weight Loss Percentage in Excel: 5 Easy Ways
Before commencing the calculation, set the cell format in percentage. As a result, Excel automatically displays the percentage rather than fractions whenever the percentage is calculated. To do so, highlight the cells, then, Go to Home > Select Percentage (from the Number section).
Keep the calculated values in percentage format to display the percentage in resultant values. Follow the latter cases to calculate weight loss percentage in Excel.
Method 1: Calculate Weight Loss Percentage Using Arithmetic Formula
A typical arithmetic formula can calculate the weight loss percentage. Subtracting the subsequent weight and then dividing by the initial weight results in a weight loss percentage.
Step 1: Paste the below typical formula in any cell (i.e., D6).
=(C6-$C$5)/ABS($C$5)*-1
In the formula, the ABS function passes the absolute value of any number.
Step 2: As the cells are previously formatted in Percentage. Hit ENTER then, drag the Fill Handle to display all the weight loss percentages for each 15-day interval.
Method 2: Assign Minimum Value Using MIN Function in Percentage Calculation
Sometimes, we want to find the overall weight loss based on spread-weight data over months. In that case, we need to find the minimum weight within a range to deduct from the starting weight. Excel’s MIN function does the job.
Step 1: Use the following formula in any blank cell (i.e., E5).
=(C5-MIN(C5:C15))/C5The MIN function just fetches the minimum weight within the range (C5:C15).
Step 2: Press ENTER to display the weight loss percentage in E5.
If you don’t pre-format the cells in Percentage, Excel merely shows the decimals as weight loss percentages.
Method 3: Using LOOKUP Function to Bring Last Value in Percentage Calculation
Similar to Method 2, we can use the LOOKUP function to auto-find the last weight from the range.
Step 1: Type the below formula in any cell (i.e., E5).
=(C5 - LOOKUP(1,1/(C5:C15<>""),C5:C15))/C5Inside the LOOKUP function 1 is the lookup_value, 1/(C5:C15<>””) is the lookup_vector, C5:C15 is the result_vector.
Step 2: To apply the formula use the ENTER key and Excel returns the overall weight loss percentage.
Method 4: Pass Most Recent Value Using OFFSET-COUNT
In some cases, users have tens of weight data and it’s hard to get around with it. We can use the OFFSET function to assign row and column numbers from where Excel gets the latest weight value to subtract. The syntax of the OFFSET function is
OFFSET (reference, rows, cols, [height], [width])The arguments define
reference; starting (provided as cell or range), to count rows or columns from.
rows; number of rows below from the reference.
cols; number of columns right of the reference.
height; number of the rows to return. [Optional]
width; number of columns to return. [Optional]
Step 1: Write the following formula in any desired cell.
=(C5-OFFSET(C5,0,COUNT(C5:K5)-1))/$C$5Comparing the formula to syntax, C5 is reference, 0 is rows, and COUNT(C5:K5)-1 is cols. The OFFSET portion of the formula fetches the rightmost weight value to deduct from the initial one.
Step 2: After pressing ENTER, you’ll get the overall percentage weight loss considering the rightmost value as the latest weight value.
The OFFSET formula is specially used for horizontally inputted entries. You can use the typical Arithmetic Formula in this case. However, it’s become inefficient to use the Arithmetic Formula in the Method of a huge dataset.
Method 5: Calculate Weight Loss Percentage Using INDEX-COUNT to Fetch Last Value
What we did with the OFFSET function in the previous case, we can do a similar thing with the INDEX function. We can use the INDEX function to insert the last row value from the given range.
Step 1: Insert the following formula in any cell.
=(INDEX(C5:C15,COUNT(C5:C15),1)-C5)/C5The INDEX portion of the formula declares C5:C15 as an array, COUNT(C5:C15) as row_num, and 1 as column_num. The COUNT function passes the row_num of the given range (i.e., C5:C15).
Step 2: Using the ENTER key insert the formula. Afterward, Excel displays the overall weight loss percentage with a minus (–) sign. The minus sign indicates the lessened amount in weight occurred over the observed period of time.
Download Excel Workbook
Conclusion
In this article, we demonstrate multiple formulas to calculate weight loss percentage in Excel. All the formulas are capable, but you can use any one of them according to your data type. Comment, if you have further inquiries or have anything to add.
