Need to add to nested IF
 

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",$R1 1<=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($R1 1<40,30,IF($R11<80,20,15)))))),0))

Thanks in advance for any help.

Need to add to nested IF
 

=IF($BH11=0,0, [original formula] )

replace "[original formula]" with your original formula without the
equal sign in front.

--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm



"sernst" wrote in message ...
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",$R1 1<=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($R1 1<40,30,IF($R11<80,20,15)))))),0))

Thanks in advance for any help.

Need to add to nested IF
 

On Fri, 1 Aug 2008 22:51:01 -0700, sernst
wrote:

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",$R 11<=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($R 11<40,30,IF($R11<80,20,15)))))),0))

Thanks in advance for any help.


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

Need to add to nested IF
 

"Lars-Åke Aspelin" wrote in message ...

On Fri, 1 Aug 2008 22:51:01 -0700, sernst
wrote:

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

Thanks, should have looked at the nesting levels before I answered,
and you've greatly simplified the formula.

Need to add to nested IF
 

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.

"Lars-Ã…ke Aspelin" wrote:


On Fri, 1 Aug 2008 22:51:01 -0700, sernst
wrote:

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",$R 11<=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($R 11<40,30,IF($R11<80,20,15)))))),0))

Thanks in advance for any help.


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