Lookup Question

[Rodney Edmonds]

I am trying to figure out how to set up a function that will allow me to be
able to do the following:

I'm trying to set up a chart in Excel "4 Columns" by "13 Rows". I will be
recording an actual Weight in each one on every Monday of the year. What I
would like for the function to calculate is as follows:

Take the Weight for the First Monday and subtract it from the Goal Weight
(Located in another Cell) and report the final value (amount of weight left
to reach goal) in the outcome cell. I would like for this to progress from
block to block within the group of cells with the outcome cell showing the
latest recorded weight each time.

Examples:

1st Monday 197 Subtract Goal Weight Cell 180 = Outcome Cell 17
2nd Monday 195 Subtract Goal Weight Cell 180 = Outcome Cell 15
3rd Monday 196 Subtract Goal Weight Cell 180 = Outcome Cell 16

Thanks in advance for reading my post and any help I receive.

 

[T. Valko]

There are at least 52 Mondays in a year.

I'm trying to set up a chart in Excel "4 Columns" by "13 Rows".

So, it sounds like each column of 13 rows is a "quarter's worth of Mondays".

I would suggest that you use a single column of 52 rows to do this. It would
be *much* easier. Then all you need to do is lookup the last entry and
subtract the goal from that:

Goal cell = C1 = 180

A1 = 1st Mon date
A2 = 2nd Mon date
A3 = 3rd Mon date
A52 = 52nd Mon date

B1 = 1st Mon weight
B2 = 2nd Mon weight
B3 = 3rd Mon weight
B52 = 52nd Mon weight

Outcome cell formula:

=IF(COUNT(B1:B52),LOOKUP(1000,B1:B52)-C1,"")

 

[Rodney Edmonds]

Thanks Biff. That was a great help.

R. Edmonds

There are at least 52 Mondays in a year.


So, it sounds like each column of 13 rows is a "quarter's worth of Mondays".

I would suggest that you use a single column of 52 rows to do this. It would
be *much* easier. Then all you need to do is lookup the last entry and
subtract the goal from that:

Goal cell = C1 = 180

A1 = 1st Mon date
A2 = 2nd Mon date
A3 = 3rd Mon date
A52 = 52nd Mon date

B1 = 1st Mon weight
B2 = 2nd Mon weight
B3 = 3rd Mon weight
B52 = 52nd Mon weight

Outcome cell formula:

=IF(COUNT(B1:B52),LOOKUP(1000,B1:B52)-C1,"")

 

[T. Valko]

You're welcome. Thanks for the feedback!