Adding an Extra condition to an IF statement

[Grimlocc]

Hello,

I am trying to add an extra condition into my formula but I can’t make it work.

So far, what I have is this:

=IF(C28=B11,1, IF(C28=B11-1,1, IF(C28=B11-2,1, IF(C28=B11-3,1, IF(C28=B11-4,1, IF(C28=B11-5,1,0))))))

Which is basically saying, if C28 is equal to or less than B11 by up to -5, return the number ‘1’. This is fine and works how I want.

What I am trying to do is add something extra in that means the value returned will be a zero rather than a 1 if the value in cell B11 is a zero.

I had done this:

=IF(AND(B11<>0)&C28=B11,1, IF(C28=B11-1,1, IF(C28=B11-2,1, IF(C28=B11-3,1, IF(C28=B11-4,1, IF(C28=B11-5,1,0))))))

Which did work to an extent but failed when a higher number than zero was in B11 and another number in a different cell but linked to B11 matched that higher number.

Does anyone have any idea how I might go about doing this?

Thanks very much

 

[Nine Zero]

Hello,

Try this formula out

=IF(B11=0,0,IF(AND(B11-C28>-1,B11-C28<6),1,0))

 

[arthurbr]

Perhaps =IF(or(B11=0,C28<<b11,c28>b11-5,C28>b11),0,1)</b11,c28>

 

[Grimlocc]

Brilliant! Both of these appear to work perfectly - I'll put them through a bit more testing but so far, so good. Thanks very much

 

[arthurbr]

Formula won't paste - sending a message to admin<b11)<b11)<b11)< html=""></b11)<b11)<b11)<>

 

[Peter_SSs]

Formula won't paste - sending a message to admin<b11)<b11)<b11)< html=""></b11)<b11)<b11)<>

See here, especially post 5