Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Product formula help
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Product formula help
Try
=SUMPRODUCT(PRODUCT(B2:B5+1))-1 If this post helps click Yes --------------- Jacob Skaria "txsharla" wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Product formula help
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? "Jacob Skaria" wrote: Try =SUMPRODUCT(PRODUCT(B2:B5+1))-1 If this post helps click Yes --------------- Jacob Skaria "txsharla" wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Product formula help
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" wrote: 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? "Jacob Skaria" wrote: Try =SUMPRODUCT(PRODUCT(B2:B5+1))-1 If this post helps click Yes --------------- Jacob Skaria "txsharla" wrote: 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Product formula help
You're absolutely right, Tom & Jacob. I must have included an incorrect range
when I initially tried the formula. Thanks so very much! "Tom Hutchins" wrote: 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" wrote: 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? "Jacob Skaria" wrote: Try =SUMPRODUCT(PRODUCT(B2:B5+1))-1 If this post helps click Yes --------------- Jacob Skaria "txsharla" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Product Code and Product Description setup | Excel Worksheet Functions | |||
I need a product key for my Trail product, 2007 Microsoft Office s | Setting up and Configuration of Excel | |||
formula for product not working | Excel Worksheet Functions | |||
Sum product the right formula? | Excel Discussion (Misc queries) | |||
Product formula help | Excel Worksheet Functions |