Need a formula to change data result weekly.

[Karen K]

I have a column of dates (I4:I30) with a column next to it for data that has
a new number in it weekly (J4:J30) for 26 weeks that is deducted from a
constant. I then have the result in J2. I can manually change the formula in
J2 weekly to have that weeks current result, but want to have it change
automatically when the weekly number is input in J4:J30. I'm sure there has
to be a way to do this.

 

[Guest]

Can you give me an example of the type of value in J4:J30 and the formula
used in J2 to help me better understand your challenge.

J

 

[Karen K]

The value in J4:J30 is "this weeks weight" (this particular week it is in
J13) and the formula in J2 is "starting weight"-"this weeks weight"
(starting weight is in M2, hidden). So, right now the formula in J2 is
=SUM(M2-J13). I have this "total loss result" plastered in a huge cell - big
& bold as a "pat on the back". I have just manually changed the "J" part of
it each week to show the current progress.

Thanks so much for trying to help!
Karen

 

[Guest]

If the weight loss is consistent, you could use the following formula:
=M2-(MIN(J4:J30))

This formula looks for the smallest value in the range of J4:J30 and
subtracts it from M2.

If this is not sufficient, I would consider using a dynamic range. I'm not
very good at this but I think that the dynamic range should allow you to
subtract the last cell (cell in the highest row number) from M2. A reference
to dynamic range information is here:
http://www.ozgrid.com/Excel/DynamicRanges.htm

Hope this is of some help.

 

[Karen K]

Thanks so much, that will work (for now at least) & I'll look into the other
way just in case those values don't stay consistent!

 

[Karen K]

Nevermind on my last post (even though I don't see it). I got a formula to
work! =M2-(Last_Cell_value(J4:J30))
Thanks to all anyway!