forumula too long error
yogi
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
=PRODUCT(C5:C128+1)
entered with ctrl + shift & enter
--
Regards,
Peo Sjoblom
"yogi" <yo...@discussions.microsoft.com> wrote in message
news:A3FB683F-7055-4360...@microsoft.com...
yogi
+ shift & enter"
Peo Sjoblom
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" <yo...@discussions.microsoft.com> wrote in message
news:D8684571-15F7-4461...@microsoft.com...
yogi
terms....
Peo Sjoblom
posted.
--
Regards,
Peo Sjoblom
"yogi" <yo...@discussions.microsoft.com> wrote in message
news:A1704522-1EA7-4AE3...@microsoft.com...
Dave Peterson
=SUMPRODUCT(PRODUCT(C5:C128+1))
without the ctrl-shift-enter stuff.
--
Dave Peterson
Square Peg
<yo...@discussions.microsoft.com> wrote:
>ok i saw the curly parenthesis...but then what...im not seeing how to add the
>terms....
This was new and confusing to me as well. After a little
experimenting, I see how to do it, but I still don't understand how it
works.
Here's how to enter it:
1. Type the formula Peo provided exactly, but do NOT press Enter:
=PRODUCT(C5:C128+1)
2. While still in edit mode in that cell, press Ctrl-Shift-Enter. That
is, hold down the Ctrl and Shift keys and then press Enter. This
causes Excel to convert the formula to an "Array Formula". This is
something I knew nothing about. A little research indicates that it is
a very powerful feature.
In your example, it is the equivalent of the product of the sums in
your original, expanded formula, but in a nice compact form that
allows you to easily change the range.
Square Peg
<peo.s...@mvps.org> wrote:
>One way
>
>
>=PRODUCT(C5:C128+1)
>
>
>entered with ctrl + shift & enter
That is amazing. I have never seen anything like that. I've done a
little reading on "array formulas", but the documentation is terrible
(or I am dumber that I thought).
Can you explain the "C5:C128+1" syntax?
What is the general syntax?
Square Peg
<pete...@verizonXSPAM.net> wrote:
>or
>
>=SUMPRODUCT(PRODUCT(C5:C128+1))
>
>without the ctrl-shift-enter stuff.
Wow. Another synrtax that I have never seen before.
What does the "C5:C128+1" do? I tried entering it without the
SUMPRODUCT (=PRODUCT(C5:C128+1)" and it failed. What does the
SUMPRODUCT do that allows this expanded syntax?
The original series was a product of sums, not a sum of products. Your
solution is counter-intuitive to me. Can you offer any explanation why
it is structured the way it is and how it works?
BTW: Thanks for the example. I really want to understand this.
Dave Peterson
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.
--
Dave Peterson
Square Peg
<pete...@verizonXSPAM.net> wrote:
>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.
I get that it's looping, but what is the general syntax?
What else can I put there? Just constants?
I tried
=sum(c1:c3+(c1*2))
and it just added "23*c1" to each cell.
I also tried
=sum(c1+1:c3)
and got an error.
The syntax is odd. I would have expected something like
{=SUM(D5:D7)+1}
which just does the sum and *then* adds 1, or
{=(SUM(D5:D7)+1)}
which does soemthing that I can't figure out.
Pete_UK
http://www.cpearson.com/excel/ArrayFormulas.aspx
Hope this helps.
Pete
On Aug 21, 3:33 am, Square Peg <Square...@Round.Hole> wrote:
> On Wed, 20 Aug 2008 14:59:30 -0700, "Peo Sjoblom"
>
Roger Govier
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
--
Regards
Roger Govier
"Square Peg" <Squa...@Round.Hole> wrote in message
news:gropa45e84jo1rlj8...@4ax.com...
yogi
add another (1+c#)-1....so i am still wondering... do i do something like
=(SUMPRODUCT(PRODUCT(C5:C128+1))-1)*(1+c129)-1
???