Calculating percentage using data from other worksheets?

Creating a spreadsheet for "Biggest Loser" contest at our organization (see attached xls). The average Team weight loss (measured in percentage) needs to be calculated in O3, O9, O15, & O21 for each of the 4 teams. For team 1, the math would go as follows:

('1-1'!C4 + '1-2'!C5 + '1-3'!C6 + '1-4'!C7) / ('1-1'!B4 + '1-2'!B5 + '1-3'!B6 + '1-4'!B7)

This formula adds up the starting weight for Team 1 in cells B4:B7 (from each respective worksheet) and divides it by their weight at the end of week 1 C4:C7 (from each respective worksheet). This part is easy enough to make work. However, I need O3, O9, O15, and O21 to stay current, dividing only the most current week with data by the original weigh in. These are as follows:

Column B is your control, the inital weigh in.
Colulmn C is W1 (the end of week 1 weigh in).
Column D is W2
Column E is W3 ... and so on through W12

How do I write a formula or VBA for Column O (Weekly team results) that ignores the weight of previous weeks and only goes based on the most current week. (ie. if column E is blank, then calculate for O based on Column D)

password protected sheets...ugh. Get rid of all that stuff until you're done designing the workbook.


This basic formula retrieves the last value in a range of values:

=LOOKUP(2, 1/(C4:N4<>""),C4:N4)

You can add a team up by stringing them together:

=LOOKUP(2, 1/(C4:N4<>""),C4:N4) + LOOKUP(2, 1/(C5:N5<>""),C5:N5) + LOOKUP(2, 1/(C6:N6<>""),C6:N6) + LOOKUP(2, 1/(C7:N7<>""),C7:N7)

ANd an average would just divide that result by 4.

Sorry about the password protected worksheets. I have uploaded an unprotected copy in my original post.

I don't want to do anything with averages. What we want to measure is the total weight loss, by percentage, of the entire team. If the entire team weighs 680Lbs to start, and then weighs 668 at the end of week 1, then the total team has lost 1.7% of their original weight.

At the end of week 2, we no longer care about the weight at the end of week 1. If the team weighs 664 at the end of week 2, we are just dividing 664 by the original weight of 680 to show that they have no lost a total of 2.4% (as a team) since the contest began.

Sheet references only go at the beginning of a range reference:

=LOOKUP(2, 1/('1-1'!C4:N4<>""),'1-1'!C4:N4)

NOTE: Your cell is formatted as percentage, so you really need two sums in this formula, one formula sums up the first week, then divides that into the sum of the "last weigh in" on record.

=(LOOKUP(2, 1/(B4:N4<>""),B4:N4) + LOOKUP(2, 1/(B5:N5<>""),B5:N5) + LOOKUP(2, 1/(B6:N6<>""),B6:N6) + LOOKUP(2, 1/(B7:N7<>""),B7:N7)) / SUM(B4:B7)

You're missing the end of the formula, the SUM of W1 you divide into that big formula. See post #6 again for the full example.