value of last row in column
Posted by DKHenderspn on September 20, 2000 2:21 PM
I've got a simple spreadsheet that calculates certain values in the last column. The number of rows will vary depending on the particular inputs. I need a formula that will look at the cell in the last row in that column. BTW, the values subsequent to the last row are blank, not 0, as the formula uses """". I can use a reference cell to identify that row if needed.
Posted by DKHenderson on September 21, 2000 7:37 AM
Posted by DKHenderson on September 21, 2000 8:05 AM
Let me try this example to explain what I am attempting to do. Construct a spreadsheet starting in row 5 that has the year in col A, investment amount in column B and accumulated balance in col C. In col B row 1 I have listed the annual investment amount and in col B row 2 the annual rate of return. In col B row 3 I have listed the number of years. In col B row 4 is the calculated ending value. That is the cell fo which I need a formula. As the number of years will vary, the number should be the last # in col C but the row will change depending upon the number of years. Does this help?
Posted by david on September 21, 2000 10:51 PM
=endingamount(B1,B2,B3)
just input this funtion
Function endingamount(InitialAmount As Long, YearlyRate As Variant, Years As Integer) As Variant
Count = InitialAmount
For x = 1 To Years
Count = Count + Count * YearlyRate
Next x
endingamount = Count
End Function
Posted by David on September 20, 0100 10:48 PM
I am sorry i missed that? Iread it a few times and still don't understand your question?
If the column is column f your formula to find the last cell is
range("f1").end(xldown) 'this is the value of the last cell
range("f1").end(xldown).row 'returns the row number of the last cell
I hope this helps if not please explain more. Although It's late and I am a little sleepy
