Need to add to nested IF

  • Thread starter Thread starter sernst
  • Start date Start date
S

sernst

I'm brain dead and tired of looking at this.

I need to add this primary condition: IF($BH11=0,0,...
to this formula:

=IF(OR($S11="7000",$S11="8000",AND($S11="1000",$R11<=0.001),AND($S11="1000",$D$7="Y")),IF(AND($S11="1000",$D$7="Y"),$C$7,21),IF($S11="1000",IF($R11<1,90,IF($R11<5,75,IF($R11<10,50,IF($R11<25,35,IF($R11<40,30,IF($R11<80,20,15)))))),0))

Thanks in advance for any help.
 
=IF($BH11=0,0, [original formula] )

replace "[original formula]" with your original formula without the
equal sign in front.
 
I'm brain dead and tired of looking at this.

I need to add this primary condition: IF($BH11=0,0,...
to this formula:

=IF(OR($S11="7000",$S11="8000",AND($S11="1000",$R11<=0.001),AND($S11="1000",$D$7="Y")),IF(AND($S11="1000",$D$7="Y"),$C$7,21),IF($S11="1000",IF($R11<1,90,IF($R11<5,75,IF($R11<10,50,IF($R11<25,35,IF($R11<40,30,IF($R11<80,20,15)))))),0))

Thanks in advance for any help.
Click to expand...


If you don't have Excell 2007 I think that you might have run into a
limit of the number of nested IF statements.

Try to simplify this formula, e.g. like this

The part

IF($R11<1,90,IF($R11<5,75,IF($R11<10,50,IF($R11<25,35,IF($R11<40,30,IF($R11<80,20,15))))))

can be replaced with

=LOOKUP($R11,{-999,1,5,10,25,40,80},{90,75,50,35,30,20,15})

make sure that the first number, -999, is less than the least possible
number in cell $R11.

After this change you can wrap the formula in another if, as already
suggested

=IF($BH11=0,0, the rest of your formula goes here)

Hope this helps / Lars-Åke
 
Lars-Åke Aspelin said:
If you don't have Excell 2007 I think that you might have run into a
limit of the number of nested IF statements.

Try to simplify this formula, e.g. like this

The part

IF($R11<1,90,IF($R11<5,75,IF($R11<10,50,IF($R11<25,35,IF($R11<40,30,IF($R11<80,20,15))))))

can be replaced with

=LOOKUP($R11,{-999,1,5,10,25,40,80},{90,75,50,35,30,20,15})

make sure that the first number, -999, is less than the least possible
number in cell $R11.

After this change you can wrap the formula in another if, as already
suggested

=IF($BH11=0,0, the rest of your formula goes here)

Hope this helps / Lars-Åke
Click to expand...

Thanks, should have looked at the nesting levels before I answered,
and you've greatly simplified the formula.
 
BEAUTIFUL. I had forgotten about the array form of Lookup. That opens the
possibilty of working with more values in $R which will please my people.
 
Back
Top