View Single Post
  #4   Report Post  
Harlan Grove
 
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(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))))))))

....

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)