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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
# 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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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 | |
|
|
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 |