forumula too long error

[yogi]

i cannot add terms to this equation...i just need a couple more! does anyone
have any ways around this? here is the formula...it is a compounding formula:

=(1+C5)*(1+C6)*(1+C7)*(1+C8)*(1+C9)*(1+C10)*(1+C11)*(1+C12)*(1+C13)*(1+C14)*(1+C15)*(1+C16)*(1+C17)*(1+C18)*(1+C19)*(1+C20)*(1+C21)*(1+C22)*(1+C23)*(1+C24)*(1+C25)*(1+C26)*(1+C27)*(1+C28)*(1+C29)*(1+C30)*(1+C31)*(1+C32)*(1+C33)*(1+C34)*(1+C35)*(1+C36)*(1+C37)*(1+C38)*(1+C39)*(1+C40)*(1+C41)*(1+C42)*(1+C43)*(1+C44)*(1+C45)*(1+C46)*(1+C47)*(1+C48)*(1+C49)*(1+C50)*(1+C51)*(1+C52)*(1+C53)*(1+C54)*(1+C55)*(1+C56)*(1+C57)*(1+C58)*(1+C59)*(1+C60)*(1+C61)*(1+C62)*(1+C63)*(1+C64)*(1+C65)*(1+C66)*(1+C67)*(1+C68)*(1+C69)*(1+C70)*(1+C71)*(1+C72)*(1+C73)*(1+C74)*(1+C75)*(1+C76)*(1+C77)*(1+C78)*(1+C79)*(1+C80)*(1+C81)*(1+C82)*(1+C83)*(1+C84)*(1+C85)*(1+C86)*(1+C87)*(1+C88)*(1+C89)*(1+C90)*(1+C91)*(1+C92)*(1+C93)*(1+C94)*(1+C95)*(1+C96)*(1+C97)*(1+C98)*(1+C99)*(1+C100)*(1+C101)*(1+C102)*(1+C103)*(1+C104)*(1+C105)*(1+C106)*(1+C107)*(1+C108)*(1+C109)*(1+C110)*(1+C111)*(1+C112)*(1+C113)*(1+C114)*(1+C115)*(1+C116)*(1+C117)*(1+C118)*(1+C119)*(1+C120)*(1+C121)*(1+C122)*(1+C123)*(1+C124)*(1+C125)*(1+C126)*(1+C127)*(1+C128)-1

 

[Peo Sjoblom]

One way


=PRODUCT(C5:C128+1)


entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom

 

[yogi]

thanks peo. im trying to use this....what does this mean "entered with ctrl
+ shift & enter"

 

[Peo Sjoblom]

Type in the formula in a cell and instead of pressing enter to get out of
the cell you press ctrl + shift and then enter,
if done correctly it will enclose the formula with curly parenthesis
{formula}

--


Regards,


Peo Sjoblom

 

[yogi]

ok i saw the curly parenthesis...but then what...im not seeing how to add the
terms....

 

[Peo Sjoblom]

What terms? The formula I gave you is the equivalent of the "formula" you
posted.

--


Regards,


Peo Sjoblom

 

[Dave Peterson]

or

=SUMPRODUCT(PRODUCT(C5:C128+1))

without the ctrl-shift-enter stuff.

 

[Dave Peterson]

It's essentially doing a loop through each cell in c5:c128 (adding 1 to each
cell).

=product(c5:c128)
(No array entering)
would multiply c5*c6*c7*...*c128

But since you want to add 1 to each cell, you need to "tell excel to loop" with
the ctrl-shift-enter.

 

[Roger Govier]

Hi

I would imagine that the OP had percentage values in his cells in the range
C5:C128 and therefore needed the +1 to ensure that not just the interest
itself was not being accumulated but the Principal + Interest was being
accumulated.

If you had £100 and had a figure of 10% in cell C5, 100*10% would give a
result of 10
If you use =100*(C5+1), it would evaluate to 100*(1.1) = 110 i.e. the
original sum plus the added 10% value.

Using Sumproduct, is another way of creating an array formula, without using
ctrl+shift+enter

To see for yourself what is happening, use a small range of C5:C7. Enter 2%,
3% and 4% in the cells and use the formula
=SUMPRODUCT(PRODUCT(C5:E5+1))
Highlight the cell with the formula
View>Toolbars>Formula Auditing and you will get a floating toolbar.
Click on the Evaluate Fx icon at the far right of he toolbar and you will be
able to step through the formula and see how Excel is making the
calculations.

To really understand the Sumproduct formula, take a look at Bob Phillips
excellent treatise on this subject
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Pete_UK]

Chip Pearson has some notes on array formulae here:

http://www.cpearson.com/excel/ArrayFormulas.aspx

Hope this helps.

Pete

 

[yogi]

but does it include the -1 at the end... that is part of the forumula. I
add another (1+c#)-1....so i am still wondering... do i do something like

=(SUMPRODUCT(PRODUCT(C5:C128+1))-1)*(1+c129)-1

???