formula too long!

I have the following formula:
(POISSON(1,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(3,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(4,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(5,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(6,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(7,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(1,0.5*(C1-Sheet1!C52),FALSE))

and need to add more, is there anyway I can reduce the size because i am being told it is too long!

Thanks

"phil2006" <phil2006.2abbhz_1151834401.8705@excelforum-nospam.com> wrote in
message news:phil2006.2abbhz_1151834401.8705@excelforum-nospam.com...
>
> I have the following formula:
<SNIP long formula>
> and need to add more, is there anyway I can reduce the size because i
> am being told it is too long!

The solution I found was simply to spread the formula over two (og more)
cells, and then simply hiding the "extra" colum.

Hope this helps

/Dann

To start I would create a name (Insert>Name>Define...) of say Half with a
refers to value of

=0.5*(C1+Sheet1!C52)

then another for one formula of say P0 with a refers to value of

=(POISSON(0,Half,FALSE))

and then use

(POISSON(1,0.Half,FALSE))*P0+
(POISSON(2,0.Half,FALSE))*P0+
(POISSON(2,0.Half,FALSE))*P0+
(POISSON(3,0.Half,FALSE))*P0+
(POISSON(4,0.Half,FALSE))*P0+
(POISSON(5,0.Half,FALSE))*(POISSON(6,0.5*(C1-Sheet1!C52),FALSE))+
(POISSON(7,0.Half,FALSE))*P0+
(POISSON(2,0.Half,FALSE))*(POISSON(1,0.5*(C1-Sheet1!C52),FALSE))

or take it further and replace FALSE by 0

(POISSON(1,0.Half,0))*P0+
(POISSON(2,0.Half,0))*P0+
(POISSON(2,0.Half,0))*P0+
(POISSON(3,0.Half,0))*P0+
(POISSON(4,0.Half,0))*P0+
(POISSON(5,0.Half,0))*(POISSON(6,0.5*(C1-Sheet1!C52),0))+
(POISSON(7,0.Half,0))*P0+
(POISSON(2,0.Half,0))*(POISSON(1,0.5*(C1-Sheet1!C52),0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"phil2006" <phil2006.2abbhz_1151834401.8705@excelforum-nospam.com> wrote in
message news:phil2006.2abbhz_1151834401.8705@excelforum-nospam.com...
>
> I have the following formula:
>
(POISSON(1,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE)
)+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALS
E))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FA
LSE))+(POISSON(3,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),
FALSE))+(POISSON(4,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52
),FALSE))+(POISSON(5,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(6,0.5*(C1-Sheet1!C
52),FALSE))+(POISSON(7,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1
!C52),FALSE))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(1,0.5*(C1-Shee
t1!C52),FALSE))
>
>
> and need to add more, is there anyway I can reduce the size because i
> am being told it is too long!
>
> Thanks
>
>
> --
> phil2006
> ------------------------------------------------------------------------
> phil2006's Profile:
http://www.excelforum.com/member.php...o&userid=35092
> View this thread: http://www.excelforum.com/showthread...hreadid=557653
>

Enter as an array formula with Ctrl+Shift+Enter

=SUM(POISSON({1,2,2,3,4,5,7,2},0.5*B1,FALSE)*POISSON({0,0,0,0,0,6,0,0},0.5*B2,FALSE))


b1= =C1+Sheet1!C52
b2= =C1-Sheet1!C52

with C12=4, C52=2 I returned a result of 0.4991536 using your posing and the
solution above.

HTH

"phil2006" wrote:

>
> I have the following formula:
> (POISSON(1,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(3,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(4,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(5,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(6,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(7,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(1,0.5*(C1-Sheet1!C52),FALSE))
>
>
> and need to add more, is there anyway I can reduce the size because i
> am being told it is too long!
>
> Thanks
>
>
> --
> phil2006
> ------------------------------------------------------------------------
> phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092
> View this thread: http://www.excelforum.com/showthread...hreadid=557653
>
>

....Posing! .... posting!

"Toppers" wrote:

> Enter as an array formula with Ctrl+Shift+Enter
>
> =SUM(POISSON({1,2,2,3,4,5,7,2},0.5*B1,FALSE)*POISSON({0,0,0,0,0,6,0,0},0.5*B2,FALSE))
>
>
> b1= =C1+Sheet1!C52
> b2= =C1-Sheet1!C52
>
> with C12=4, C52=2 I returned a result of 0.4991536 using your posing and the
> solution above.
>
> HTH
>
> "phil2006" wrote:
>
> >
> > I have the following formula:
> > (POISSON(1,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(3,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(4,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(5,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(6,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(7,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(1,0.5*(C1-Sheet1!C52),FALSE))
> >
> >
> > and need to add more, is there anyway I can reduce the size because i
> > am being told it is too long!
> >
> > Thanks
> >
> >
> > --
> > phil2006
> > ------------------------------------------------------------------------
> > phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092
> > View this thread: http://www.excelforum.com/showthread...hreadid=557653
> >
> >

Bob,
The second half of the formula uses C1-Sheet1!C52 so (I believe)
using your solution will give an incorrect result as in your response both
portions are using C1+Sheet1!C52.

"Bob Phillips" wrote:

> To start I would create a name (Insert>Name>Define...) of say Half with a
> refers to value of
>
> =0.5*(C1+Sheet1!C52)
>
> then another for one formula of say P0 with a refers to value of
>
> =(POISSON(0,Half,FALSE))
>
> and then use
>
> (POISSON(1,0.Half,FALSE))*P0+
> (POISSON(2,0.Half,FALSE))*P0+
> (POISSON(2,0.Half,FALSE))*P0+
> (POISSON(3,0.Half,FALSE))*P0+
> (POISSON(4,0.Half,FALSE))*P0+
> (POISSON(5,0.Half,FALSE))*(POISSON(6,0.5*(C1-Sheet1!C52),FALSE))+
> (POISSON(7,0.Half,FALSE))*P0+
> (POISSON(2,0.Half,FALSE))*(POISSON(1,0.5*(C1-Sheet1!C52),FALSE))
>
> or take it further and replace FALSE by 0
>
> (POISSON(1,0.Half,0))*P0+
> (POISSON(2,0.Half,0))*P0+
> (POISSON(2,0.Half,0))*P0+
> (POISSON(3,0.Half,0))*P0+
> (POISSON(4,0.Half,0))*P0+
> (POISSON(5,0.Half,0))*(POISSON(6,0.5*(C1-Sheet1!C52),0))+
> (POISSON(7,0.Half,0))*P0+
> (POISSON(2,0.Half,0))*(POISSON(1,0.5*(C1-Sheet1!C52),0))
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "phil2006" <phil2006.2abbhz_1151834401.8705@excelforum-nospam.com> wrote in
> message news:phil2006.2abbhz_1151834401.8705@excelforum-nospam.com...
> >
> > I have the following formula:
> >
> (POISSON(1,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE)
> )+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALS
> E))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FA
> LSE))+(POISSON(3,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),
> FALSE))+(POISSON(4,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52
> ),FALSE))+(POISSON(5,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(6,0.5*(C1-Sheet1!C
> 52),FALSE))+(POISSON(7,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1
> !C52),FALSE))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(1,0.5*(C1-Shee
> t1!C52),FALSE))
> >
> >
> > and need to add more, is there anyway I can reduce the size because i
> > am being told it is too long!
> >
> > Thanks
> >
> >
> > --
> > phil2006
> > ------------------------------------------------------------------------
> > phil2006's Profile:
> http://www.excelforum.com/member.php...o&userid=35092
> > View this thread: http://www.excelforum.com/showthread...hreadid=557653
> >
>
>
>

Building on Bob's proposal:

Enter with Ctrl+Shift+Enter

=SUM(POISSON($I$1:$I$8,PS1,0)*POISSON($J$1:$J$8,PS2,0)

PS1: is named range =0.5*(C1+Sheet1!C52)
PS2: is named range =0.5*(C1-Sheet1!C52)

I1:I8 contain values for PS1
J1:J8 contain values for PS2

HTH


"Toppers" wrote:

> Enter as an array formula with Ctrl+Shift+Enter
>
> =SUM(POISSON({1,2,2,3,4,5,7,2},0.5*B1,FALSE)*POISSON({0,0,0,0,0,6,0,0},0.5*B2,FALSE))
>
>
> b1= =C1+Sheet1!C52
> b2= =C1-Sheet1!C52
>
> with C12=4, C52=2 I returned a result of 0.4991536 using your posing and the
> solution above.
>
> HTH
>
> "phil2006" wrote:
>
> >
> > I have the following formula:
> > (POISSON(1,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(3,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(4,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(5,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(6,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(7,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(1,0.5*(C1-Sheet1!C52),FALSE))
> >
> >
> > and need to add more, is there anyway I can reduce the size because i
> > am being told it is too long!
> >
> > Thanks
> >
> >
> > --
> > phil2006
> > ------------------------------------------------------------------------
> > phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092
> > View this thread: http://www.excelforum.com/showthread...hreadid=557653
> >
> >

You are right, my P0 should be

=(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Toppers" <Toppers@discussions.microsoft.com> wrote in message
news:6A2754A7-399D-4BCF-8824-9C1EC7FC8E95@microsoft.com...
> Bob,
> The second half of the formula uses C1-Sheet1!C52 so (I
believe)
> using your solution will give an incorrect result as in your response both
> portions are using C1+Sheet1!C52.
>
> "Bob Phillips" wrote:
>
> > To start I would create a name (Insert>Name>Define...) of say Half with
a
> > refers to value of
> >
> > =0.5*(C1+Sheet1!C52)
> >
> > then another for one formula of say P0 with a refers to value of
> >
> > =(POISSON(0,Half,FALSE))
> >
> > and then use
> >
> > (POISSON(1,0.Half,FALSE))*P0+
> > (POISSON(2,0.Half,FALSE))*P0+
> > (POISSON(2,0.Half,FALSE))*P0+
> > (POISSON(3,0.Half,FALSE))*P0+
> > (POISSON(4,0.Half,FALSE))*P0+
> > (POISSON(5,0.Half,FALSE))*(POISSON(6,0.5*(C1-Sheet1!C52),FALSE))+
> > (POISSON(7,0.Half,FALSE))*P0+
> > (POISSON(2,0.Half,FALSE))*(POISSON(1,0.5*(C1-Sheet1!C52),FALSE))
> >
> > or take it further and replace FALSE by 0
> >
> > (POISSON(1,0.Half,0))*P0+
> > (POISSON(2,0.Half,0))*P0+
> > (POISSON(2,0.Half,0))*P0+
> > (POISSON(3,0.Half,0))*P0+
> > (POISSON(4,0.Half,0))*P0+
> > (POISSON(5,0.Half,0))*(POISSON(6,0.5*(C1-Sheet1!C52),0))+
> > (POISSON(7,0.Half,0))*P0+
> > (POISSON(2,0.Half,0))*(POISSON(1,0.5*(C1-Sheet1!C52),0))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "phil2006" <phil2006.2abbhz_1151834401.8705@excelforum-nospam.com> wrote
in
> > message news:phil2006.2abbhz_1151834401.8705@excelforum-nospam.com...
> > >
> > > I have the following formula:
> > >
> >
(POISSON(1,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE)
>
> )+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FA
LS
> >
E))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FA
> >
LSE))+(POISSON(3,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),
> >
FALSE))+(POISSON(4,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52
>
> ),FALSE))+(POISSON(5,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(6,0.5*(C1-Sheet1
!C
> >
52),FALSE))+(POISSON(7,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1
> >
!C52),FALSE))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(1,0.5*(C1-Shee
> > t1!C52),FALSE))
> > >
> > >
> > > and need to add more, is there anyway I can reduce the size because i
> > > am being told it is too long!
> > >
> > > Thanks
> > >
> > >
> > > --
> > > phil2006
> >
> ------------------------------------------------------------------------
> > > phil2006's Profile:
> > http://www.excelforum.com/member.php...o&userid=35092
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=557653
> > >
> >
> >
> >

This is one of those instances where parsing the formula for patterns raises
some questions.

There is a pattern: Poisson({n},(+),)*(Poisson(0,(-),) which holds for n=
1,2,3,4, and 7.

{n}=2 is repeated. Is that intentional?

Also, there is a break in the pattern at n=5 and n=6 where you have
Poisson(5,(+)) * Poisson (6,(-)). Is that intentional?

And, finally, at the end, you have a Poisson(2,(+))*Poisson(1,(-)). Is that
intentional?

I would address this as follows:

In a separate cell, enter the Poisson(0,(-)) formula.

Put the n values in a column and the corr. Poisson(n,(+)) in the adjacent
column.

Finally, put the Poisson(n,(+))-Poisson(0,(-)) calculations in the next
adjacent column.

Add everything up in a cell below the range that contains the above
analysis.

The result will be dramatically transaparent, easy to read, understand,
debug, and maintain.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <phil2006.2abbhz_1151834401.8705@excelforum-nospam.com>,
phil2006.2abbhz_1151834401.8705@excelforum-nospam.com says...
>
> I have the following formula:
> (POISSON(1,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(3,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(4,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(5,0.5*(C1
+Sheet1!C52),FALSE))*(POISSON(6,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(7,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(0,0.5*(C1-Sheet1!C52),FALSE))+(POISSON(2,0.5*(C1+Sheet1!C52),FALSE))*(POISSON(1,0.5*(C1-Sheet1!C52),FALSE))
>
>
> and need to add more, is there anyway I can reduce the size because i
> am being told it is too long!
>
> Thanks
>
>
> --
> phil2006
> ------------------------------------------------------------------------
> phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092
> View this thread: http://www.excelforum.com/showthread...hreadid=557653
>
>