Figuring daily average...function ???

[Fish]

Hello all,

I'm new to working with function other than the very basic ones (sum,
product, etc...). I'm using Excel 2002. Being new, I'll try and explain what
I'm trying to do clearly, but feel free to ask for any clarification if any
of this isn't clear.


My current spreadsheet:

I'm keeping results of an investment. It is changing often so I'm keeping
track of weekly results. I also want to be able figure what the daily
earnings were between two dates to get a good idea of the average daily
growth. I need help figuring this daily growth figure.

Right now I have the original starting date of this investment in B2
(10/10/05)
The original dollar figure I started the investment with is in B3

The new weekly total is already calculated in O34 and changes as the
investment changes

The new (current ) entry date (3/6/06) is in W2
I then enter each new weekly balance of the investment (found in O34) along
row 3 (current entry is in W3)


What function can I add into my spreadsheet that will calculate the average
daily growth between two dates. For example I want to calculate the average
daily growth between the starting date (B2) of this investment and the
current date (W2) and dollar amounts from those dates (B3 and W3). I assume
the function will need to figure the amount of days between the dates and
then divide that by the difference in dollar amount. How can this be done???


Thanks in advance for your time and any help !!!!!!!!!!!!!!!!

 

[Bob Phillips]

=(W3-B3)/(W2-B2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Fish]

Great, thanks.

One more question if you don't mind.

The function you suggested works great as is, but I'll have to change the W2
and W3 each week to the new entry (next week will be X2 and X3 and so on
along rows 2 and 3.

Is there a way to have that function automatically use the most
recent/current entry for the calculation?



THANKS AGAIN !!!!

 

[Bob Phillips]

Try this

=(INDEX(3:3,MATCH(9.99999999999999E+307,3:3))-B3)/(INDEX(2:2,MATCH(9.9999999
9999999E+307,2:2))-B2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Fish]

Thanks Bob,

I tried to paste that formula into my spreadsheet, but it gave me a couple
error messages.

The first message says I am missing a parenthesis in the function. It looked
like the missing parenthesis might need to go at the very end of the formula
so I added it and tried again.

Then I got a different error message saying I have "entered too few
arguments for this function".


Am I doing something wrong?


Thanks.

 

[Bob Phillips]

NG wrap-around.

Try

=(INDEX(3:3,MATCH(9.99999999999999E+307,3:3))-B3)
/(INDEX(2:2,MATCH(9.99999999999999E+307,2:2))-B2)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Fish]

Great, thanks.

I did say I was a newbie, right...

 

[Bob Phillips]

It's one of the most often-encountered difficulties people have with
answers. Usually with VBA, but sometimes with functions as you found <bg>

Glad you're sorted anyway

Bob