Hi all,

I am putting together a spreadsheet for a weight watching club. There are 6
members, and a weight entry will be recorded each friday from 08/01/10 -
01/04/10. The weight will be entered in total lbs e.g. 142.25 and the data
range is B26:N31. Each members weight will be recorded in this range, i.e.
Andrews weight will be tracked in row 26 from cells B to N as the weeks go
by.

I have then converted the total weight in lbs to stones and lbs as this is
easier for everyone to understand. The formula i have used for this is as
follows:
=INT(SUM(B26)/14)&" st "&MOD(B26,14)&" lbs"

Therefore there are two grids tracking the weight in different formats.

What i then want to do, is compare the latest weight figures to the starting
figure on the 08/01/10. The starting figures will be summarised in Cells
B8:B13, the latest figurest will be summarised in cells C8:C13 and i then
want to show how many pounds have been lost in cells D8-D13, all in the
stones and lbs format.There are two things i can't figure out to achieve this:
1) Firstly, i want the latest date figure to be pre-populated in Cell C7
i.e. above the latest weight figures. The dates are already enter in the grid
in row 25, cells B to N, so is it possible for excel to look along the date
range, and pick out the latest date with figures entered in rows 26-31. i.e.
if there is no data in column H, then the computer will know to use the date
from column G?
2) Secondly, is it possible from the summarising data range B8:C13 to
calculate how many pounds have been lost for each member and display this in
stones and lbs format. If it is the case that the stone and lbs format data
can't be used, then there is always the initial data range where the data is
entered in total lbs. I'm guessing this calculation would involve LOOKUP in
cells B26:N31 to pick out the latest values and there compare that to the
starting figure, and then display this in stones and lbs. Not so sure if this
can all be done in one cell. If needs be, i can hide cells in another sheet,
i.e. to pick out the latest weight value, and then use the formula above to
display it in stones and lbs.
3) lastly, if it was possible, i want to display the percentage change of
the latest weight figure to the starting figure. Again this could be done
from cells in another sheet as it will probably be based on the total lbs
data.

Sorry this is so lengthly, but i would be very grateful if anyone can help.

Many thanks

Carol

1) C7: =INDEX($25:$25,MATCH(C8,$26:$26,0))

2) C8: =LOOKUP(2,1/B26:N26,B26:N26)
D8: =INDEX($25:$25,MATCH(C8,$26:$26,0))

HTH

Bob

"carol" wrote in message
...
Hi all,

I am putting together a spreadsheet for a weight watching club. There are
6
members, and a weight entry will be recorded each friday from 08/01/10 -
01/04/10. The weight will be entered in total lbs e.g. 142.25 and the data
range is B26:N31. Each members weight will be recorded in this range, i.e.
Andrews weight will be tracked in row 26 from cells B to N as the weeks go
by.

I have then converted the total weight in lbs to stones and lbs as this is
easier for everyone to understand. The formula i have used for this is as
follows:
=INT(SUM(B26)/14)&" st "&MOD(B26,14)&" lbs"

Therefore there are two grids tracking the weight in different formats.

What i then want to do, is compare the latest weight figures to the
starting
figure on the 08/01/10. The starting figures will be summarised in Cells
B8:B13, the latest figurest will be summarised in cells C8:C13 and i then
want to show how many pounds have been lost in cells D8-D13, all in the
stones and lbs format.There are two things i can't figure out to achieve
this:
1) Firstly, i want the latest date figure to be pre-populated in Cell C7
i.e. above the latest weight figures. The dates are already enter in the
grid
in row 25, cells B to N, so is it possible for excel to look along the
date
range, and pick out the latest date with figures entered in rows 26-31.
i.e.
if there is no data in column H, then the computer will know to use the
date
from column G?
2) Secondly, is it possible from the summarising data range B8:C13 to
calculate how many pounds have been lost for each member and display this
in
stones and lbs format. If it is the case that the stone and lbs format
data
can't be used, then there is always the initial data range where the data
is
entered in total lbs. I'm guessing this calculation would involve LOOKUP
in
cells B26:N31 to pick out the latest values and there compare that to the
starting figure, and then display this in stones and lbs. Not so sure if
this
can all be done in one cell. If needs be, i can hide cells in another
sheet,
i.e. to pick out the latest weight value, and then use the formula above
to
display it in stones and lbs.
3) lastly, if it was possible, i want to display the percentage change of
the latest weight figure to the starting figure. Again this could be done
from cells in another sheet as it will probably be based on the total lbs
data.

Sorry this is so lengthly, but i would be very grateful if anyone can
help.

Many thanks

Carol