Google sheets: Calculate weight lost

Hi guys,

Is anybody able to guide me on how I can calculate weight lost in my spreadsheet here:

https://docs.google.com/spreadsheets...it?usp=sharing

See Cell G13.. it references cell B11-T11 which will have weight populated (which will fluctuate). I think what I have is halfway to what I want. but my formulas results in G13 including the following week (i.e. the current weight in the calcualation, is there a way I can setup the formula to not include my existing weight?)

Thank you!

Please post your sheet here.

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

That sheet is protected so we can't edit it.

I can see this formula in G13 and it doesn't seem right.

=INDEX(B11:T11,1,COUNT(B11:T11))-A11

As far as I can see =INDEX(B11:T11,1,COUNT(B11:T11)) is always going to return the last value in B11:T11, even if that value is 0 and the rest of the range is 0.

HI Glenn,

My sheet is attached (it's converted to excel now from google).

Thank you!

Hi Norie,

Sorry there's a lot of protected cells.

What are you implying with the formula being wrong and returning the last value? I guess I don't want the last value to be counted... could you possibly tell me what the alternative formula should be?

Unprotect the cells!!! How can you expect people to help properly without giving them proper access to the data?

Sorry Ali, I have done this now.

Originally Posted by rayted

Hi Norie,

Sorry there's a lot of protected cells.

What are you implying with the formula being wrong and returning the last value? I guess I don't want the last value to be counted... could you possibly tell me what the alternative formula should be?

Norie - I am now using this: =A5-INDEX(B5:T5,1,COUNT(B5:T5))

however if one puts on weight (the figure in cells B5:T5 would be red and this seems to mess up the calculation)

So for example losing weight, the calculation above works. If one puts on weight, the total weight lost is messed up because it is subtracting from the weight put on. How would you suggest I overcome this? Thank you!

??? Change A5 from

=IFERROR(__xludf.DUMMYFUNCTION("IMPORTRANGE(""1Us7CRfa21tK-I7UVL9NpZT7K2cFbKEp-PJLLejXGbA8/edit?usp=sharing"", ""Sheet2!B8:N8"")
"),"199.9lbs")

to

=IFERROR(__xludf.DUMMYFUNCTION("IMPORTRANGE(""1Us7CRfa21tK-I7UVL9NpZT7K2cFbKEp-PJLLejXGbA8/edit?usp=sharing"", ""Sheet2!B8:N8"")
"),199.9)

Originally Posted by Glenn Kennedy

??? Change A5 from

=IFERROR(__xludf.DUMMYFUNCTION("IMPORTRANGE(""1Us7CRfa21tK-I7UVL9NpZT7K2cFbKEp-PJLLejXGbA8/edit?usp=sharing"", ""Sheet2!B8:N8"")
"),"199.9lbs")

to

=IFERROR(__xludf.DUMMYFUNCTION("IMPORTRANGE(""1Us7CRfa21tK-I7UVL9NpZT7K2cFbKEp-PJLLejXGbA8/edit?usp=sharing"", ""Sheet2!B8:N8"")
"),199.9)

Thank you Glenn - but this is not importing my data (on google sheets anymore?) so i`m not sure this is working? i have unprotected my cells and can you advise if i am doing something wrong?

I don't know the syntax used by googlesheets. Try this:

=IFERROR(__xludf.DUMMYFUNCTION("IMPORTRANGE(""1Us7CRfa21tK-I7UVL9NpZT7K2cFbKEp-PJLLejXGbA8/edit?usp=sharing"", ""Sheet2!B8:N8"")
"),"199.9")

The lbs is turning it into text and the formula falls over.

In Excel, another fix 9USING YOUR ORIGINAQL FORMULA IN a5)is:

A11:
=LEFT(A5,LEN(A5)-3)+0

b11:
=B5-A11 copied across

Originally Posted by Glenn Kennedy

In Excel, another fix 9USING YOUR ORIGINAQL FORMULA IN a5)is:

A11:
=LEFT(A5,LEN(A5)-3)+0

b11:
=B5-A11 copied across

Hi Glenn,

I still don't think this fulfils my requirement

AS i need to share this with multiple individuals I am using google sheets. I guess I could try using excel online? However, my main sticking point is still the issue with counting the amount of weight lost!

Update:

Hi Glenn/all

Please see this sheet, sheet3 tab:

=SUMIF($A$1:$D$7,"<0"). - I have added in a new formula, but is there a way I can perhaps instruct the formula not to count the previous weight? or maybe exclude the count if B3 (weight from previous weigh in) is the same as A2(previous weigh in)?

Looks like I need a SUMIF with an except/exception criteria. I'm just not sure how to apply this..... thanks for your help in advance