Calculating Average Weight Gain Per Week

**erin1684** · 07-12-2010, 10:07 PM

Hello,
I'm new to the forum, so please let me know if I'm not describing my situation correctly.
I need to calculate the average weight gained per week for multiple patients at the hospital where I work. (this would work with weight loss as well...I'm just in the opposite business)

Currently, I collect weights every day and enter them into a spreadsheet (dates are the column headings, patient names are the row headings). I need to prepare a report quarterly that describes how many patients met their weight gain goal (2-4 pounds per week)...i.e. I need to get the average weight per week and then use those averages to calculate the change in weight per week. I am looking for a way to auto-calculate these numbers if at all possible as I have a lot of patients to analyze per quarter (p.s. I'm definitely an Excel novice, but I can teach myself pretty quickly). Another caveat: the start dates for each week are not on the same day for each patient, as new patients admit on different days over time (i.e. patient 1's admit weight is 100 on 7/1 in the example below, but patient 2's admit weight is 120 on 7/3).

This is how my spreadsheet is set up*:
Date: 7/1 7/2 7/3 7/4 7/5 7/6 7/7 7/8 7/9 7/10
Patient 1:100 99.5 100.2 101 101.5 100.4 101.2 101.5 102 101.9 etc...
Patient 2: 120 121 121.3 etc....
* Looks like when I post this, the dates do not match up with the weights...100 should go under 7/1, 95 under 7/2, 100.2 then 120 under 7/3 etc...)

Any idea how I can do this?
Thank you in advance for your time! I really appreciate it!

**DonkeyOte** · 07-13-2010, 03:12 AM

I'm sure people will be happy to try and assist but in this instance I would suggest posting a sample file with some dummy data and also outline expected results based on said data.

To attach a file use the paperclip icon in the reply window, if you can not see it first click GoAdvanced

**Coaster** · 07-13-2010, 09:35 AM

is this what you wanted?

this file may need revising, especially because the formula is almost undecipherable but let us know if i'm on the right track

**erin1684** · 07-13-2010, 01:20 PM

Originally Posted by DonkeyOte

I'm sure people will be happy to try and assist but in this instance I would suggest posting a sample file with some dummy data and also outline expected results based on said data.

To attach a file use the paperclip icon in the reply window, if you can not see it first click GoAdvanced

Thanks for your reply! I uploaded a sample spreadsheet. You will see on the spreadsheet that each patient's first weight begins on different dates (and their last weights are on different days as well, depending on when they discharged from our hospital).

Expected results:
I need an average weekly weight for each patient (so day 1 through day 7, until their last day).
From there I need to calculate the change in weight between each week (we are looking for gains typically).
So if patient 1 was an average of 100 pounds on week 1, and then on week two their average weekly weight was 102, then they gained 2 lbs per week in that period.
Does that make sense? Again, thanks for your help!

**erin1684** · 07-13-2010, 01:23 PM

Originally Posted by Coaster

is this what you wanted?

this file may need revising, especially because the formula is almost undecipherable but let us know if i'm on the right track

Thanks for working on this! Your spreadsheet looks like it gives me the information I need, but I have no idea how to translate that to my own spreadsheet! I also need to take the data one step further...you gave me the average weekly weight for each patient...from there I need to calculate the change in average weekly weight from week to week (i.e. 2 lbs gained).

I uploaded a sample spreadsheet in reply to DonkeyOte's post above...perhaps that will help make my situation more clear?

Please let me know if you require further explanation! Thanks again, I really appreciate your help!