Excel formula daily average of weight

Question

I am looking to set up an excel spreadsheet to calculate cattle weight gain per day. We weigh our cattle once a week so column A will be a date and Column B will be the weight on that day(day of the week may change). I want excel to give me the average daily weight gain. is the a formula to do this or a pivot table? Please post the "how to" to make this work. Thank you

Answer 1

With Date in column A and weight in column B, row 1 has labels, use this in C3 and copy down

=(B3-B2)/(A3-A2)

Note that row 6 has only 6 days difference, row 7 has 8 days difference. The formula calculates weight difference divided by number of elapsed days.

Answer 2

Let's assume you have headers, so your first row of data is row 2. You can add another column, say, C. Start in row 3 with the formula:

=B3-OFFSET(B3,-1,0)

This will give you the difference between the last weight and the previous. Drag this down to the last row. Format what you have as a table. To do this highlight your table, then go to the Insert ribbon, and click on insert table. Verify the range for the table. On the table design check the Total Row checkbox. Click the dropdown arrow at the bottom of the daily weight change column and select "Average". When you need to add data, just click on the last row (not a cell, but the row label) and on the ribbon click Insert > Insert Sheet Rows. You may need to resort by date, but you won't need to copy formulas. Excel will do this automatically.