Weight chart Please see page 3 for final query

[natasha_tracy]

Hello everyone -

I am trying to create a weight chart, which works in stones, pounds and ounces. My first query is that each time I enter the weight in for each day, I need it to deduct 1ib 4oz from the weight I enter. At present I have seperate columns for stones, pounds and ounces. However, if I try to do a simple lbs minus 4 it doesn't work, because sometimes the pounds are less than four - so instead of taking the stone column down by one and then deducting the remaining pounds, it just puts the pounds columns to -3 or similar. The same applies to the oz columns - good old imperial eh!

Also, I would like my table to work out weight lost over a period of time - so, I have my starting weight at the top and then I want my most recent weight to be taken from the starting weight, leaving weight lost - I can't get this to work either. Is there a way to make it so that the calculation uses the most recent weight entry and does so automatically?

If anyone has any solutions to these queries, I would be very grateful...as I have to caculate this for a number of people and it would be much quicker if it did it all automatically upon entering the most recent weight.

 

[ADAMC]

Hi Natasha,

can you perhaps post an example of your sheet.

Not sure if i understand right....im a little confused but thats easily done

 

[ADAMC]

Not sure if this helps but found a formula on line and adapted it to suit a single cell:

=SUM(A1,INT(SUM(A2)/14))&" Stones "&MOD(SUM(A2,INT(SUM(A3)/16)),14)&" Pounds "& MOD(SUM(A3),16)&" Ounces"

Where A1 is stones A2 is pounds and A3 is ounces

I put this formula in B2 and it seems to calculate correctly, is this along the lines of what you were looking for?

 

[natasha_tracy]

Hello! Yes of course below is the weight chart:

• A B C D
  Date Stones Pounds Ounces
  Start Weight 10 11 0
  28/08/07 10 8 4
  29/08/07 10 8 4
  30/08/07 10 7 0

I want to deduct 1 pound and 4 ounces from the weight I enter each day, without calculating it manually or in my head - I figured that if I set up a separate table as follows:

Weight to deduct: Stones Pounds Ounces
0 1 4


However, I can't just do 10 stone 7 pounds 0 ounces, minus 1 pound, 4 ounces, because the ounces column will take it to -4. Presumably, because it's imperial. So my first issue is that I can't take away the weight...

My second issue is that I want to work out weight lost - so I want the spreadsheet to use the start weight and take away the most recent entry into the weight chart, in this case on the 30/08/07 and tomorrow it would be the weight on 01/09/07 - is there a way of doing this automatically without having to change the formula everytime?

thanks

 

[natasha_tracy]

Whoops - the formatting didn't work - my example doesn't look right... can you tell I'm new to this?? Not sure how to give an example??

 

[natasha_tracy]

Hi - not perfect, but I think this makes it a little clearer - try, try again... also - your formula was great that you gave me, but I don't think it's quite what I was looking for??

A                      B                 C                   D 
Date                Stones          Pounds           Ounces 
Start Weight     10                11                  0 
28/08/07          10                8                   4 
29/08/07          10                8                   4 
30/08/07          10                7                   0

I want to deduct 1 pound and 4 ounces from the weight I enter each day, without calculating it manually or in my head - I figured that if I set up a separate table as follows:

Weight to deduct: Stones   Pounds    Ounces 
                           0           1            4

However, I can't just do 10 stone 7 pounds 0 ounces, minus 1 pound, 4 ounces, because the ounces column will take it to -4. Presumably, because it's imperial. So my first issue is that I can't take away the weight...

My second issue is that I want to work out weight lost - so I want the spreadsheet to use the start weight and take away the most recent entry into the weight chart, in this case on the 30/08/07 and tomorrow it would be the weight on 01/09/07 - is there a way of doing this automatically without having to change the formula everytime?

thanks[/code]

 

[ADAMC]

Its okay i think i can figure out from what yu posted....(Weight is not one of my strong points but ill try to help)

Question:

Lets assume:
Column A date B is stones C is pounds D is ounces
Do you have formulas to calculate the weights or are you manually changing the weights each day?

I put todays date in A1 B1 is Stones C1 pounds D1 ounces
on the next row down you always want it to be the previous days weight - 1 pound 4 ounces.......?

 

[wisewood]

I wrote a very simple, but very long PHP script for doing this a while ago. I will dig it out and see if i can make it work for VBA.

 

[natasha_tracy]

Its okay i think i can figure out from what yu posted....(Weight is not one of my strong points but ill try to help)

Question:

Lets assume:
Column A date B is stones C is pounds D is ounces
Do you have formulas to calculate the weights or are you manually changing the weights each day?

I put todays date in A1 B1 is Stones C1 pounds D1 ounces
on the next row down you always want it to be the previous days weight - 1 pound 4 ounces.......?

Yes - that's exactly it...

 

[Wookie]

weight conversion.xls
	A	B	C	D
1	Stones	Pounds	Ounces
2	15	0	0	15Stones0Pounds0Ounces
3	14	8	7	14Stones8Pounds7Ounces
4	1	-8	-7	0Stones5Pounds9Ounces
Sheet1