A few problems involving LOOKUP and SUMPRODUCT...

[carol]

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

 

[Bob Phillips]

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

 

[p45cal]

I think we can do away with your second range of converted weights
altogether (unless you want to keep it for display purposes).

It hinges round this formula which I placed in cell A25, but when all
other formulae are in place you can click and drag cell A25 somewhere
less obtrusive.
It's ARRAY-ENTERED (hold down Ctrl and Shft while pressing the Enter
key):
=MAX(IF($B$26:$N$31>0,COLUMN($B$26:$N$31),2))-2

For the date header in C7 you can try this formula:
=OFFSET($B$25,,$A$25)

For B8:B13 enter this formula in B8 and copy down to B13:
=INT(B26/14)&" st "&MOD(B26,14)&" lbs"

For C8:C13 in C8 put:
=INT(OFFSET($B26,,$A$25)/14)&" st "&MOD(OFFSET($B26,,$A$25),14)&" lbs"
and copy down to C13.

For the change in D8:

Code:
--------------------
=IF((OFFSET($B26,,$A$25)-$B26)<0,"-","") & INT(ABS(OFFSET($B26,,$A$25)-$B26)/14)&" st "&MOD(ABS(OFFSET($B26,,$A$25)-$B26),14)&" lbs"
--------------------

copied to D13.

If there's a problem with that long formula and line-wrapping have a
look at it at thecodecage.com (link to the thread there below, I think)
and copy/paste from there.

For the percent change put this formula in E8:
=OFFSET($B26,,$A$25)/$B26-1
copied down to E13, and format the cells to '%' with as many decimal
points as you want.

Note that because the formula looks at the *whole* range B26:N31 and
returns the rightmost column with anything in it, people for whom you've
not yet entered current weights in that column show in the summary as
having lost 100% of their weight!

Now you can drag cell A25 elsewhere.