Need help to return last value in row and also a value in column specified number of columns to left

[jjs98]

Hi there,

Many thanks in advance for any help/solutions on this (I'm sure) very simple problem from a newbie.

I have a row of numerical data which is updated daily. On a different tab in the same sheet, I want to create a formula with divides the last value in the row by a value in the same row that is a specified number of columns behind the last one.

An example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]1/1/13[/TD]
[TD]1/2/13[/TD]
[TD]1/3/13[/TD]
[TD]1/4/13[/TD]
[TD]1/5/13[/TD]
[TD]1/8/13[/TD]
[TD]1/9/13[/TD]
[TD]1/10/13[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]9[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


I want a formula that takes the last value ("10" on 1/10/13) and divides it by the corresponding value 5 (busines) days earlier (which would be 1/3/13 or "9"), which is easy enough of course. What I want to know is how to get the formula to update automatically, as I enter new values for each day.

I have figured out how to return the last value in a row using =LOOKUP(9.9999999999999+307, row#:row#), which I could theoretically use to get the updated last number in the row. The question is how to get the trailing number.

All the bets and thanks for taking the time!

Jim

 

[martindwilson]

try
=LOOKUP(9.9999999999999+307,2:2)/INDEX(2:2,MATCH(9.9999999999999+307,2:2,1)-5)

 

[jjs98]

try
=LOOKUP(9.9999999999999+307,2:2)/INDEX(2:2,MATCH(9.9999999999999+307,2:2,1)-5)

Thank you so much for this, it worked wonderfully! Just out of curiosity (and so that I understand this in the future) the "1" in the last entry in the MATCH piece, what does that do?

Again, thanks so much! Hopefully I can return the favor some day once I get better at this...

Jim

 

[martindwilson]

MATCH(9.9999999999999+307,2:2,1) the 1 means
If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: but a quirk of that is if it isn't in any particular order it matches 9.9999999999999+307 with the last value

 

[jjs98]

MATCH(9.9999999999999+307,2:2,1) the 1 means
If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: but a quirk of that is if it isn't in any particular order it matches 9.9999999999999+307 with the last value

Interesting.... This is just a quirk of the way the command functions? I think that is the type of thing that makes these forums so useful--there's no way I would have been able to guess that that quirk existed!

Actually in applying this I ran up against another problem that is confusing me to no end.

I have 6 or so rows of data that update daily like described at first. I have then skipped a row and put two more rows of data for benchmarking. It looks something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Day 1[/TD]
[TD]Day 2[/TD]
[TD]Day 3[/TD]
[TD]Day 4[/TD]
[TD]Day 5[/TD]
[TD]Day 6[/TD]
[TD]Day 7[/TD]
[TD]Day 8[/TD]
[TD]Day 9[/TD]
[/TR]
[TR]
[TD]Data string 1[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data string 2[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data string 3[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data string 4[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data string 5[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data string 6[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Benchmark 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Benchmark 2[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So the formula with INDEX and MATCH working perfectly to get the last value entered for the Data strings and to get the change versus a trailing number (5 days I used in my example). The problem is that as I pulled the formula down to the two benchmarks, I got an incorrect result for the first and a #N/A for the second. I could see perhaps why the first was screwed up b/c of the #s not going all the way back to the start (leftmost column), but the 2nd Benchmark is very peculiar indeed. The numbers are large (thousands) but i can't imagine that would have made a difference.

To backtest and see if I could figure it out myself I did the simple LOOKUP(9.9999999999999+307, Row#:Row#) to both benchmarks. The Benchmark 1 returns a value in the middle of the data string (not the rightmost entry), which explains the calculation error but not why it is doing it. The second gives you a #N/A. The calculation error, when you show the steps in the calc, happens right at the start when LOOKUP(317 attempts to apply to the Row#:Row#.

Confused and thanks for any help again!
Jim