Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > formula too long!
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1
Default 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's Profile: http://www.excelforum.com/member.php...o&userid=35092
View this thread: http://www.excelforum.com/showthread...hreadid=557653

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9
Default formula too long!
"phil2006" wrote in
message ...

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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10,593
Default formula too long!
To start I would create a name (InsertNameDefine...) 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" wrote in
message ...

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))*(POISSO N(0,0.5*(C1-Sheet1!C52),FA
LSE))+(POISSON(3,0.5*(C1+Sheet1!C52),FALSE))*(POIS SON(0,0.5*(C1-Sheet1!C52),
FALSE))+(POISSON(4,0.5*(C1+Sheet1!C52),FALSE))*(PO ISSON(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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 4,339
Default formula too long!
Enter as an array formula with Ctrl+Shift+Enter

=SUM(POISSON({1,2,2,3,4,5,7,2},0.5*B1,FALSE)*POISS ON({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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 4,339
Default formula too long!
....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)*POISS ON({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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 4,339
Default formula too long!
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 (InsertNameDefine...) 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" wrote in
message ...

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))*(POISSO N(0,0.5*(C1-Sheet1!C52),FA
LSE))+(POISSON(3,0.5*(C1+Sheet1!C52),FALSE))*(POIS SON(0,0.5*(C1-Sheet1!C52),
FALSE))+(POISSON(4,0.5*(C1+Sheet1!C52),FALSE))*(PO ISSON(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




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 4,339
Default formula too long!
Building on Bob's proposal:

Enter with Ctrl+Shift+Enter

=SUM(POISSON($I$1:$I$8,PS1,0)*POISSON($J$1:$J$8,PS 2,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)*POISS ON({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


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10,593
Default formula too long!
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" wrote in message
...
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 (InsertNameDefine...) 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" wrote
in
message ...

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))*(POISSO N(0,0.5*(C1-Sheet1!C52),FA

LSE))+(POISSON(3,0.5*(C1+Sheet1!C52),FALSE))*(POIS SON(0,0.5*(C1-Sheet1!C52),

FALSE))+(POISSON(4,0.5*(C1+Sheet1!C52),FALSE))*(PO ISSON(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






Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1,071
Default formula too long!
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 ,
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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't add 7th IF statement to long formula. manxman Excel Worksheet Functions 7 June 8th 06 08:23 AM
More than 3 conditional formats? Ltat42a Excel Discussion (Misc queries) 12 January 6th 06 11:26 AM
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 05:18 PM
Formula too long glenlisa Excel Discussion (Misc queries) 3 August 9th 05 07:34 AM
Date & Time mully New Users to Excel 4 May 23rd 05 11:56 AM


All times are GMT +1. The time now is 10:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement