Insert new raw at the end without udpating the ranges in forumla??

[alghareeb]

I have the following table

Clomun A, Colmun B
Row 4 29-Apr-05, 100
Row 5 31-May-05, 98.05
Row 6 30-Jun-05, 99.52
Row 7 31-Jul-05, 104.90
.. .. ..
Row 43 31-Jul-08, 180.44

and following formulla to calculate the compounded rate of return

=(B43/B4)^(1/((A43-A4)/365))-1

Column A is for Date data, and Column B is for monthly price data

Currenlty, the data start from row number 4 and it ends at row number 43.
At every month end I add new row after the last data I have. Every time I insert a row I have to manually update the reference range in the formula to cover the new row inserted which is annoyying. i.e. Now I need to insert a new row which should be row number 44, but the reference range convered by the forumulla is upto row 43. So how I can make the worksheet automatically get the reference range in the forumulla updated so to include any new row inserted at the end.

I'm sure there is a way by which excel can update the reference range as and when I insert a new raw with data.

[martindwilson]

change
=(B43/B4)^(1/((A43-A4)/365))-1
to =(B43/$B$4)^(1/((A43-$A$4)/365))-1
then
simply drag down
if you are only inserting data into cola+b you caan do it in advance

[arthurbr]

Hi,
you might want to use Dynamic Ranges so the range wil adapt automatically
Cheers

[NBVC]

Try:

=(LOOKUP(9.999999999E+307,B:B)/B4)^(1/((LOOKUP(9.999999999E+307,A:A)-A4)/365))-1

[alghareeb]

Thanks everybody for help

NBVC
Your suggestion works, but why the result is slightly different from the result I get with my orginial formulla.

Many thanks for help

[NBVC]

Can you elaborate on what you mean? All my formula does is take the last entry in column B and column A and insert them in the formula... the rest is your formula...

[alghareeb]

Sorry your formmulla works fine, it was my mistake ... thanks alot for help

It's really great forum that I was missing long time

[NBVC]

Strange...

Is there any data below row 43 at all?

Are both column A and B occupied in last row?

If yes to both questions, maybe you can attach a sample workbook so I can see what may be going on.

[alghareeb]

NBVC
Your forumulla works perfectly, you are great. Thanks so much

But I would love to know what's the logic fbehind using (LOOKUP(9.999999999E+307))

Thank you very much for help