Calculation on non-blank cell

[JordanR]

Is there a way to setup a calculation in a cell that looks to the left of it for the last two non-blank cells and performs the calculation?

Example:
Cell D1: {First non-blank}/{Second non-blank}
Cell A1: 50
Cell B1: 100
D1 would then equal 2.

If I then entered 2 in C1, D1 would then change to 50. Idea being that I could keep adding columns and never have to change the formula.

 

[Brianwarnock]

Will some Cells have a value in the new column and some not, if the answer is no have a look at Offset in help, if yes than I think code will be required.

Brian

 

[JordanR]

Nah, the idea is that I need to keep inserting colums for week1, week2, week 3, then delete those columns and put in a new column for the month, which summarizes the month's activity, and the last column does mathmatical comparisons of the last two columns. Doing this has gotten really annoying since I keep having to adjust all the formulas.

 

[Brianwarnock]

I don't understand where the blank cell comes in, perhaps I need a picture or other type of example, what Offset does is allow you to insert or delete a column next to the formula and still pick up the last 2 cols e.g.
code in C1
=OFFSET(C1,0,-1)/OFFSET(C1,0,-2)

Would become
=OFFSET(D1,0,-1)/OFFSET(D1,0,-2) in D1

If another col inserted between B and C

Brian