Formula that updates a weight change in one cell based on monthly updates

[ktraina]

I have 12 columns each named for a month of the year. In the row below, a trainer inputs a client's weight in the cells associated with the month they take the measurement. At the end of that row, I have a column titled "Monthly Change" where I have created a formula that takes the current month's weight and subtracts it from the previous month's weight….giving us their progress. Is there a way I can add to that formula to update that particular cell as the months progress without having to change the formula each month? I only want one cell that updates at the end of the row rather than having 12 separate cells that change with the updates.

Thanks so much!

 

[Stridhan]

Try this instead

=if(or(B9="",C9="")"",INDIRECT(ADDRESS(ROW(),COUNTA(B9:M9)))-B9)

Might overdo it but, use above and if, Jan weight is blank or filled but Feb weight is blank, then it gives a blank in total sumcell.

 

[ktraina]

Thanks so much. When I tried the formula you gave me, it doesn't total up the +/- weight from each of the months on a consistent basis. Am I doing something incorrectly? For the first example, the total change should be 1 and on the second it should total 2.

<colgroup><col></colgroup><tbody>
</tbody>

 

[Stridhan]

It doesnt give u the total loss? Last entered weight - first ?
Driving, no computer atm

 

[ktraina]

It doesn't, but I totally just remembered that I changed your initial formula (don't know if that matters) so that the old month was subtracted from the new month…it gave me the numbers in the way that the trainer needed them. This is what I used to get them:

=CHOOSE(COUNTA(B9:M9),"",C9-B9,D9-C9,E9-D9,F9-E9,G9-F9,H9-G9,I9-H9,J9-I9,K9-J9,L9-K9,M9-L9)

Instead of January subtracting February (B9-C9) and February subtracting March (C9-D9), it's the other way around with February's total subtracting January's total (C9-B9) and March subtracting February's total (D9-C9).

I'm not sure that makes a difference but perhaps it does.

No rush on this….drive safe.

 

[Stridhan]

So you didnt put it in P9 and enter?
I thought you meant that you just needed a total in P9.
if entered in P9 it should have looked for the last entered weight and then calculate that cell - b9, that gives the total loss, right?

or, do you enter the loss and not the new weight/month?
Still driving..

 

[ktraina]

Ok….back from a walk with the kid and the dog and looking at this worksheet again (have I said thank you for all your help so far….thanks, if I haven't). So, for the Total Change, it is a cumulative sum of all the +/- weight as the months progress. If they lost 1 pound in February (-1), lost another pound in March (-1), gained a pound in April (+1)…..they would have a total change from January of -1 pound and it would register it in P9. Each month, it would update keeping their total weight change updated cumulatively. At the end of the year, they would be able to look in cell P9 and see what their total weight loss/gain actually was for the entire year.

Sorry I'm still driving….you nuts!

 

[Stridhan]

Haha ok, so what youre telling me is, its only the loss and gain that is registered in that row, not his/her total weight/month....which actually both formulas for..
My formula for the monthly change will work for that but not the total.....it aint summing, its only subtracting =)

If only loss and gain as mentioned above, then a simple sumformula
In P9

=sum(B9:N9)

Eyy, its friday

 

[ktraina]

In that row it is what he/she weighs when they come in for their measurements. So, in January they may be 102, in February 101 (loss 1 pound), March 100 (loss 1 pound). The total change should change each month with the weight. February's total would only be -1 but then in March that number would have an additional -1 added to it so there would be -2 pounds lost cumulatively.

i hate math right now

 

[Stridhan]

in P9

=if(or(B9="",C9=""),"",INDIRECT(ADDRESS(ROW(),COUNTA(B9:N9)+1))-B9)

gotta be numbers from Jan --->, cant skip a months value, if no weight, put a - or something, otherwise it aint working.

 

[Stridhan]

wtf...use below....hate macs

=if(or(B9="",C9=""),"",INDIRECT(ADDRESS(ROW(),COUNTA(B9:M9)+1))-B9)

nighty nighty