Conditional calculations

[Summer]

I have a table with locations along a hiking trail. Another column
shows the mile posts. I want use a third column to place a sequential
number in the same row as the location and mile post where I camp each
night. The table looks like this:

Cloumn A - Location name (Springer Mountian, Hawk Mountain, etc.)
Column B - Mile marker for each location (0.25, 7.6, etc)
Column C - Sequential number representing the number of days hiked (1,
2, 3, etc)

I will put a "1" in C1 showing that is where I camped that night. I
will put a "2" in C2 showing I camped there that night. Based on the
"2", the cell in D2 will search up the table and find the "1" in C1
and calculate the mikes hiked that day as C1-B1.

In some instances there will be several rows with locations and mile
posts where I will not camp. So the formula needs to search the cells
above it for the sequential number for that day minus one, and
calculate the miles hiked that day. For example lets say that I put
the "3" in C6. Cells C3, C4 amd C5 will be empty, but C2 will contain
a "2". The formula in D6 would then subtract B2 from B6 to calculate
that days miles.

This is easy to do except for the search of the cells above.

Any ideas how to do this. I prefer a solution that uses a formula in
the cell.

Thanks

 

[Bernard Liengme]

Only part-tested, but try
=IF(C2>0,B2-INDEX($B$1:$B$20,MATCH(C2-1,$C$1:$C$20)),"")
best wishes

 

[Summer]

It worked correctly for values 1 through 9, but then failed with
values 10 or larger. I'm looking at the formual now to see if I can
figure out why it doesn't work for all values.

 

[Summer]

It works fine for all values. My table had more than 20 rows in it, so
I changed the 20 to 200 and it works fine.

Thanks

 

[Bernard Liengme]

Thanks for feedback. Glad you got it working.
Happy camping