Need a formula to change data result weekly.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Karen K - 24 Feb 2007 00:10 GMT

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.

Reply to this Message

J - 24 Feb 2007 01:31 GMT

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

> 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.

Reply to this Message

Karen K - 24 Feb 2007 02:35 GMT

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

> 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.
[quoted text clipped - 10 lines]
>> has
>> to be a way to do this.

Reply to this Message

J - 24 Feb 2007 03:58 GMT

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.

> 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"
[quoted text clipped - 20 lines]
> >> has
> >> to be a way to do this.

Reply to this Message

Karen K - 24 Feb 2007 12:28 GMT

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!

> If the weight loss is consistent, you could use the following formula:
> =M2-(MIN(J4:J30))
[quoted text clipped - 43 lines]
>> >> has
>> >> to be a way to do this.

Reply to this Message

Karen K - 24 Feb 2007 15:47 GMT

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!

> 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!
[quoted text clipped - 48 lines]
>>> >> has
>>> >> to be a way to do this.

Reply to this Message