Calculations for total body weight lost/gained.

**saitek** · 01-20-2009, 05:28 PM

Hello everyone,

I am trying to create a weight management, measurements and bodyfat spreadsheet which will automatically calculate the amount of weight or bodyfat etc gained or lost. I will then place this in a graph to show my client how well/bad they are doing.

At the moment I have these weights and all I have done is taken the highest number away from the lowest number, which works fine, but when I start adding more weights and dates etc I have to constantly re-adjust the formulae.

245
239
234
227.5
228.7
225.3

Total lost -19.7lbs

Can anyone help me to get this to work please?

Thanks in advance for any replies.

**martindwilson** · 01-20-2009, 05:31 PM

=min(a:a)-max(a:a) will give highest -lowest but that is flawed what if it goes higher than the start weight then comes down to something still higher than start weight then the result should be a net gain surely?

**jasoncw** · 01-20-2009, 05:36 PM

Martin's formula will work, assuming they are consistently losing weight. But we all know how that goes...

This may or may not work for you, depending on how your spreadsheet is set up. This assumes the start weight is in A2 (with a header in A1), there is nothing in column A except the weights, and they are entered contiguously (no blank cells).

=A2-OFFSET($A$1,COUNTA($A:$A)-1,0)

HTH

Jason

**saitek** · 01-20-2009, 05:42 PM

Very quick reply, thanks!

Although that works well, I don't think I explained what I wanted very well though. If her weight suddenly went higher than her original weight half way through, the final amount lost will be much higher than it should.

For example:
245
239
234
280.5
228.7
225.3

-55.2lbs

Although there was only -19.7lbs loss from her original weight till now. Is there anyway to kind of add and subtract one weight from the next?

Sorry for not being clear.

**jasoncw** · 01-20-2009, 05:45 PM

Saitek, did you see my reply? That will subtract the last entry from the original entry.

**saitek** · 01-20-2009, 05:50 PM

Originally Posted by jasoncw

Saitek, did you see my reply? That will subtract the last entry from the original entry.

Just seen it now, I was replying, my apologies.

I tried changing the area from A to H but I can't get it to work. Here is a screenshot to show you how it's set up. I have highlighted the correct area for clarity.

http://img233.imageshack.us/my.php?i...tweightoh9.png

**jasoncw** · 01-20-2009, 05:52 PM

Sorry, I can't see that file (blocked by firewall). Please upload to this thread by clicking the Manage Attachments button.

**saitek** · 01-20-2009, 05:57 PM

Originally Posted by jasoncw

Sorry, I can't see that file (blocked by firewall). Please upload to this thread by clicking the Manage Attachments button.

I have attached it now.

**martindwilson** · 01-20-2009, 06:02 PM

another way is to match a big number it will always give the last value and you dont have to worry about blanks or text
=A2-INDEX(A:A,MATCH(10^6,A:A))
start weight in a2
(ok iknow 10^6 is not THE big number but if someone goes over a million kilos i'll be ever so slightly surprised)

**jasoncw** · 01-20-2009, 06:10 PM

Here is a different solution, based on your workbook. Try:

=INDIRECT("R[-1]C",0)-H7

HTH

Jason

**saitek** · 01-20-2009, 06:25 PM

Originally Posted by martindwilson

another way is to match a big number it will always give the last value and you dont have to worry about blanks or text
=A2-INDEX(A:A,MATCH(10^6,A:A))
start weight in a2
(ok iknow 10^6 is not THE big number but if someone goes over a million kilos i'll be ever so slightly surprised)

This seems to work, only problem is it doesn't seem to show a negative value only when the weight has gone up.

Originally Posted by jasoncw

Here is a different solution, based on your workbook. Try:

=INDIRECT("R[-1]C",0)-H7

HTH

Jason

This one doesn't seem to work unfortunately.

----------

Thanks for all the help so far, I really appreciate it!

**jasoncw** · 01-20-2009, 06:44 PM

From your uploaded screenshot, did you include the formula in cell H13?

**saitek** · 01-20-2009, 06:56 PM

Originally Posted by jasoncw

From your uploaded screenshot, did you include the formula in cell H13?

Oh, I see where I went wrong now. I have been tweaking the design and have added in more dates etc so there is more space. See attachment.

I do apologise for messing you around, it wasn't intentional.

**jasoncw** · 01-20-2009, 07:09 PM

Ok, let's try this again.

=OFFSET(H$6,COUNTA(INDIRECT("R6C:R[-1]C",0))-1,0)-H$7

**mewingkitty** · 01-20-2009, 07:10 PM

I believe Jasons suggestion of

Please Login or Register  to view this content.

This does work, but it can not be placed in the same column as the information is posted in, since it is the last item in the column, it would return A2-self as a value, which therefore makes Excel palm-face itself.

**saitek** · 01-20-2009, 07:38 PM

Originally Posted by jasoncw

Ok, let's try this again.

=OFFSET(H$6,COUNTA(INDIRECT("R6C:R[-1]C",0))-1,0)-H$7

Thanks very much, this one seems to work very well!! I have been trying to get this all sorted pretty much the whole day!!

Thanks to everyone who helped out I appreciate all your help and time.

**jasoncw** · 01-20-2009, 07:39 PM

Glad it worked for you.

Please mark the thread solved. Thanks.

Jason

**saitek** · 01-20-2009, 07:46 PM

Originally Posted by jasoncw

Glad it worked for you.

Please mark the thread solved. Thanks.

Jason

One very last thing....

Is there anyway to add a "+" sign before any of the results that are in the positive?

**mewingkitty** · 01-20-2009, 07:54 PM

format cells, choose custom, and place

Please Login or Register  to view this content.

in the entry bar

**saitek** · 01-20-2009, 08:05 PM

Originally Posted by mewingkitty

format cells, choose custom, and place

Please Login or Register  to view this content.

in the entry bar

Just had to tweak it slightly, but it's perfect.

Thanks a lot!

Calculations for total body weight lost/gained.

LinkBack

Thread Tools

Rate This Thread

Display

Calculations for total body weight lost/gained.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions