Function problems

[SPCjcMIARNG]

I need to create a formula that can update when new entries and entered in
the rows of a single page spreadsheet.
Here is the problem. This is a standardized format for my office so all I
can do is fix their equation. The prior year numbers are on the 2nd row of
the spreadsheet, and they enter new entires in successive rows on the form so
that they can track staff strength numbers on a weekly basis for the entire
year. They want an equation that will recompute the weekly comparison to the
prior year's ttl number. I can't just use SUM because we're not tracking
growth. We're trying to track variation to the prior year, and each week the
cell I need to refer to for the current week's numbers changes because it is
one row lower.
So I need to figure out an equation that will allow me to refer to the last
cell in a column that has a value, and I need it to update as new values are
added in cells below the previous last cell to track those cells as the new
reference cells.
Thank you for your time.

 

[Bob Phillips]

This can get you the last value in column A assuming that your formula is in
A2

=MATCH(9.99999999999999E+307,A3:A65536)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Sorry, not last value, I meant the last cell index, so you need to add 2 to
get the row number.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[bpeltzer]

If your staff strength is in column B, then =OFFSET($B$1,COUNT(B:B),0) should
pick up the last numeric entry in that column.
--Bruce

 

[SPCjcMIARNG]

Would that give me the variance, or a listing of the initial value and the
current?

 

[SPCjcMIARNG]

I tired that, the "0" gives it a no column value for the count, so the
formula will always come up with 0 for an answer. When the column width is
increased to 1 to allow for the column included by the reference, the
equation spits out how many cells in that column have values not the value in
the last filled cell. The input in these cells are numerical values for
those weeks, not by name listings that are updated weekly. Thank you for the
input though.

 

[Search33]

=OFFSET($A$1,MATCH(9.99999999999999E+307,A:A)-1,0)

Assuming the numbers are in Column A

- Search