Formula for gain and loss within a column with a single cell running total

**unusually_usual** · 06-11-2011, 12:22 AM

I'm looking for a formula to calculate the total loss/gain for a given range of cells. Admittedly, this is for a weight loss/fitness worksheet to track my progress or regression. (irrelevant?)

What I am wanting (and going crazy trying to figure out) is a column of data entry and then a single cell representing the total loss or gain.

EX:

A1 = 140
A2 = 138
A3 = 139
A4 = 135
A5 = 136
A6 =
A7 =

A9 = total difference

I hope this is making sense. Between A1 and A2 there is a loss, so when A3-A7 are blank, I want A9 to represent all data differences from A1-A7 (blanks having no effect on the total). The from A2 to A3 there is a gain... etc. So I suppose I'm asking for a formula that can add AND subtract into a running total.

In addition, if there is any gain over the previous week's entry I would like that cell to be shaded by a color... if possible.

I truly hope y'all can help me with this formula. Thank you in advance.

PS I did read the forum rules; if my title is insufficient, please offer me a suggestion for improvement.

**MarvinP** · 06-11-2011, 12:29 AM

Hi and welcome to the forum,

I believe your question has an appropriate title.

Microsoft Office has lots of templates that users have submitted and you can look at. I went to Office Templates and found these samples
http://office.microsoft.com/en-us/te...in=CT010152732

You might look at a few of them to see if they do more than you hoped and also accomplish your goal.

**unusually_usual** · 06-11-2011, 09:35 AM

Hi MarvinP,

Thank you for the swift response and the warm welcome. I took a look at the link you provided me to see if you were able to find something I wasn't and unfortunately, those templates are just not offering exactly what I am wanting. I certainly hope there is a formula for what I am wanting, but I'm beginning to feel I'm asking too much of one little cell. =)

**MarvinP** · 06-11-2011, 10:22 AM

HI again,

The reason I pointed you to the templates is there are many designs for a weight tracking workbook. I feel you would need at least two columns: Date and Weight.

If you had these two you sort by date to arrange the weights. You could then do Pivot Tables showing running totals, Max, Min and other stats. You could also group the dates and show weight gain/loss by week or month or year.

I think your design allows you or keeps you from doing what you want. I think a single column of numbers where a singel cell gave multiple answers was asking too much.