# of Functions per cell

S

SUB-ZERO

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(D11:D12),IF(D9=0,SUM(D10:D12),IF(D8=0,SUM(D9:D12),IF(D7=0,SUM(D8:D12),IF(D6=0,SUM(D7:D12),IF(D5=0,SUM(D6:D12),SUM(D5:D12))))))))

Any help would be greatly appreciated

Thanks ...


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

RagDyeR

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,D1:D11)),D12,LOOKUP(9.99999999999999E+
307,D1:D11)+D12)
--

HTH,

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


contain 0's, or nulls < "" >, or
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(D11:D12),IF(D9=0,SUM(D10:D12),IF(D8=0,SUM(D9:D12)
,IF(D7=0,SUM(D8:D12),IF(D6=0,SUM(D7:D12),IF(D5=0,SUM(D6:D12),SUM(D5:D12)))))
)))

Any help would be greatly appreciated

Thanks ...


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

John Mansfield

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)*60

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.
 
H

Harlan Grove

John Mansfield said:
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)*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.
....

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

=SUM(IF(COUNTIF(D5:D11,0),INDEX(D:D,LOOKUP(2,1/(D5:D11=0),
ROW(D5:D11))),D5):D12)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top