Conditional formula

[Kierano]

Hi,

I have a formula in which I want to return a 1 to include else a 0 if
certain cells contain a value of + or - 10. Unfortunately this is returning
zeros even when a 1 should be returned.

=SUMPRODUCT(--(N57<=11),--(N57<=-11),--(Q57<=11),--(Q57<=-11),--(AB57<=11),--(AB57<=-11),1,0)

Any help gratefully appreciated.

 

[bj]

remove the ,1,0
=SUMPRODUCT(--(N57<=11),--(N57<=-11),--(Q57<=11),--(Q57<=-11),--(AB57<=11),--(AB57<=-11))
or
=SUMPRODUCT(--(abs(N57)<=11),--(abs(Q57)<=11),--(abs(AB57)<=11))
From your Description You might want "10" rather than 11 in your equations.
a simpler equation
=if(max(abs(M57),Abs(Q57),Abs(Ab57))<=11,1,0)

 

[David Biddulph]

Do you mean >=-11 ?
And I'm not sure why you're using 11 and -11, when your description talks of
"+ or - 10"?
And finally I assume that most of your formula is doing nothing because you
end up with multiplying everything by 1 (which won't change things much),
then by zero (which will). If you want to return a 1 if all your conditions
are met, and a zero if they're not, you don't need the last two terms, as
you'll get that from the multiplication of your logicals coerced to
numerical values.

 

[David Biddulph]

And even more finally, what are you trying to sum? You've got a list of
terms for the product, but without array references I can't see where the
sum comes in? You may want to look in help for the operation of the
SUMPRODUCT() function.