Product formula help

[txsharla]

I want to mathmatically link a column of monthly returns (percentages) like
this:

(1+b2)*(1+b3)*(1+b4)*(1+b5)-1 (where b2=3.05%, b3=2.54% etc...)

I know there are other ways of doing this longhand but I'm looking for the
formula to simpifly my work. Thanks!

 

[NBVC]

Try:

=PRODUCT(1+B3:B5)-1

confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { }
brackets appear around the formula


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)

 

[Jacob Skaria]

Try
=SUMPRODUCT(PRODUCT(B2:B5+1))-1

If this post helps click Yes

 

[txsharla]

Thanks, Jacob, but that's not quite right. Here are the actual returns:

-1.78%
0.48%
1.60%
-0.68%
-1.86%
-1.53%
-8.59%
-6.56%
-2.19%
-2.08%
1.03%
0.08%

The answer is -20.39% (doing it longhand). Any other ideas?

 

[NBVC]

Did you expand the ranges in the formulas to match your datarange?

Both alternatives provided give same answer if you expand the rang
accordingly

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[Tom Hutchins]

Jacob's formula is perfect; since you have more data points than indicated in
your original post, you need to edit the range in the formula to match your
data:

=SUMPRODUCT(PRODUCT(B2:B13+1))-1

Putting your actual returns in B2:B13, I got the same result you did by
computing it manually and by using the above formula.

Hope this helps,

Hutch

 

[txsharla]

You're absolutely right, Tom & Jacob. I must have included an incorrect range
when I initially tried the formula. Thanks so very much!

 

[NBVC]

okay.. so maybe I am invisible...

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[T. Valko]

okay.. so maybe I am invisible....

Right now, and for about the last week, yes, you are invisible if you post a
reply to a message that was posted through the MS web interface and *you are
not using the MS web interface*.

I'm also invisible as are a large number of the regulars that post here. The
system is broken and has been for about a week. MS does know about it.

 

[NBVC]

Right now, and for about the last week, yes, you are invisible if you
post a
reply to a message that was posted through the MS web interface and
*you are
not using the MS web interface*.

I'm also invisible as are a large number of the regulars that post
here. The
system is broken and has been for about a week. MS does know about it.

--
Biff
Microsoft Excel MVP



Office Discussion' (http://www.thecodecage.com))

Thanks Biff.. I thought I had bad B.O or something


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)

 

[T. Valko]

Those of us not using the MS web interface can see each others posts but
those using the MS web interface can't see our replies.

--
Biff
Microsoft Excel MVP

Right now, and for about the last week, yes, you are invisible if you
post a
reply to a message that was posted through the MS web interface and
*you are
not using the MS web interface*.

I'm also invisible as are a large number of the regulars that post
here. The
system is broken and has been for about a week. MS does know about it.

--
Biff
Microsoft Excel MVP



Office Discussion' (http://www.thecodecage.com))

Thanks Biff.. I thought I had bad B.O or something


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=152431

Microsoft Office Help