A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

# of Functions per cell



 
 
Thread Tools Display Modes
  #1  
Old January 22nd, 2005, 10:17 PM
SUB-ZERO
external usenet poster
 
Posts: n/a
Default # of Functions per cell

Hi

Is there any way to change the number of functions allowed in each cell?
I have a spreadsheet and I need it to handle more functions than 7.
10-15 functions wolud really work well for me.
Here is one example of what im working with, I need to have a few more
allowed functions for the formula to work correctly.
=IF(D11=0,D12,IF(D10=0,SUM(D1112),IF(D9=0,SUM(D1 012),IF(D8=0,SUM(D912),IF(D7=0,SUM(D812),IF( D6=0,SUM(D712),IF(D5=0,SUM(D612),SUM(D512))) )))))

Any help would be greatly appreciated

Thanks ...


--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-

  #2  
Old January 22nd, 2005, 11:34 PM
RagDyeR
external usenet poster
 
Posts: n/a
Default

Don't know if your "empty" cells are,
blank,
or null ( "" )
or
zeroes,
So, this may or may not work for you:

=IF(ISNA(LOOKUP(9.99999999999999E+307,D111)),D12 ,LOOKUP(9.99999999999999E+
307,D111)+D12)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


contain 0's, or nulls "" , or
"SUB-ZERO" wrote in message
...
Hi

Is there any way to change the number of functions allowed in each cell?
I have a spreadsheet and I need it to handle more functions than 7.
10-15 functions wolud really work well for me.
Here is one example of what im working with, I need to have a few more
allowed functions for the formula to work correctly.
=IF(D11=0,D12,IF(D10=0,SUM(D1112),IF(D9=0,SUM(D1 012),IF(D8=0,SUM(D912)
,IF(D7=0,SUM(D812),IF(D6=0,SUM(D712),IF(D5=0,S UM(D612),SUM(D512)))))
)))

Any help would be greatly appreciated

Thanks ...


--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-


  #3  
Old January 23rd, 2005, 08:57 PM
John Mansfield
external usenet poster
 
Posts: n/a
Default

To get around the "nested 7 levels deep" IF statement limitation you can use
binary switching logic. For example, assume the numbers 1 through 10 can be
placed in cell A1. You need to write an IF statement that includes all 10
numbers. You can write the statement similar to this ie. if A1=1,50 etc.:

=(A1=1)*51+(A1=2)*52+(A1=3)*53+(A1=4)*54+(A1=5)*55 +(A1=6)*56+(A1=7)*57+(A1=8)*58+(A1=9)*59+(A1=10)*6 0

If you need the statement to evaluate multiple conditions as True, use plus
(+) signs instead of multiplication (*) signs. Also, this technique works
for numbers only. The formula can not return text entries.

----
Regards,
John Mansfield
http:www.pdbook.com


"SUB-ZERO" wrote:

Hi

Is there any way to change the number of functions allowed in each cell?
I have a spreadsheet and I need it to handle more functions than 7.
10-15 functions wolud really work well for me.
Here is one example of what im working with, I need to have a few more
allowed functions for the formula to work correctly.
=IF(D11=0,D12,IF(D10=0,SUM(D1112),IF(D9=0,SUM(D1 012),IF(D8=0,SUM(D912),IF(D7=0,SUM(D812),IF( D6=0,SUM(D712),IF(D5=0,SUM(D612),SUM(D512))) )))))

Any help would be greatly appreciated

Thanks ...


--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-


  #4  
Old January 23rd, 2005, 11:35 PM
Harlan Grove
external usenet poster
 
Posts: n/a
Default

"John Mansfield" wrote...
To get around the "nested 7 levels deep" IF statement limitation you can
use binary switching logic. For example, assume the numbers 1 through 10
can be placed in cell A1. You need to write an IF statement that includes
all 10 numbers. You can write the statement similar to this ie. if
A1=1,50 etc.:

=(A1=1)*51+(A1=2)*52+(A1=3)*53+(A1=4)*54+(A1=5)*5 5+(A1=6)*56+(A1=7)*57
+(A1=8)*58+(A1=9)*59+(A1=10)*60

....

Bad example. This formula should be rewritten as

=IF(AND(A1=INT(A1),A1=1,A1=10),A1+50,0)

or more generally as a lookup.

"SUB-ZERO" wrote:
Here is one example of what im working with, I need to have a few more
allowed functions for the formula to work correctly.

=IF(D11=0,D12,IF(D10=0,SUM(D1112),IF(D9=0,SUM( D1012),
IF(D8=0,SUM(D912),IF(D7=0,SUM(D812),IF(D6=0, SUM(D712),
IF(D5=0,SUM(D612),SUM(D512))))))))

....

IF D11 = 0, doesn't SUM(D1112) = D12? I do see that you're trying to limit
the sum, summing only over the numbes in D512 below the bottommost 0. You
could use the following formula instead.

=SUM(IF(COUNTIF(D511,0),INDEX(D,LOOKUP(2,1/(D511=0),
ROW(D511))),D5)12)


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
IF only.....IF only.... SmokingMirror Worksheet Functions 8 August 7th, 2004 07:37 PM
Applying Functions to Another Cell ScS Worksheet Functions 1 April 2nd, 2004 03:36 PM
How do do Concatenated Cell Values Steve Arndt Worksheet Functions 3 January 21st, 2004 06:34 PM
Link or paste cells based on Date in cell Ayanna Worksheet Functions 0 December 16th, 2003 07:10 PM
Question on LOGIC ?? - PLEASE HELP PM Worksheet Functions 17 November 28th, 2003 10:13 AM


All times are GMT +1. The time now is 11:45 PM.


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