problem with formula

[Guest]

=IF(W32=1,IF(T32-U32=0,"pb",IF(AND(T32-U32>=-100,T32-U32<=50),"immaterial","")),V32)

The above formula works as is, however if the number in u is between 0 and
-100 then it doesn't return immaterial. For example if t=2 and u= -80 then
2-(-80) = 82 which isn't material but it should be. Should I use an absolute
value or something--
Libby

 

[Sandy Mann]

Libby,

T32-U32>=-100
means bigger than -100, well -99 is BIGGER then -100, -1 is BIGGER
then -100, 1 is BIGGER then -100 and so 82 is BIGGER than -100

Did you mean :
T32-U32<=-100

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Peo Sjoblom]

If T = 2 and u = -80

that is 82 but your criteria is >= - 100 which it passes AND <= 50 which is
doesn't since 82 is greater than 50 not less than or equal

However that doesn't matter since your formula fails regardless

for what condition do you want it to return V32 and for what do you want it
to return ""?

=IF(AND(W32=1,T32-U32=0),"pb",IF(AND(T32-U32>=-100,T32-U32<=50),"immaterial",""))

 

[Guest]

I guess what I mean is that is t-u or u-t is <=50 and >= -100 should be
immaterial and t-u or u-t is >50 or < -100 is left blank

 

[Rick Rothstein \(MVP - VB\)]

=IF(W32=1,IF(T32-U32=0,"pb",IF(AND(T32-U32>=-100,T32-U32<=50),"immaterial","")),V32)

The above formula works as is, however if the number in u is between 0 and
-100 then it doesn't return immaterial. For example if t=2 and u= -80
then
2-(-80) = 82 which isn't material but it should be. Should I use an
absolute
value or something--

Look at the innermost IF function call...

IF(AND(T32-U32>=-100,T32-U32<=50),"immaterial","")

When T32=2 and U=-80, your and condition evaluates to FALSE... you have the
"immaterial" text in the TRUE part of the function call, not the FALSE part
where your post seems to indicate you want it.

Rick

 

[Guest]

I guess what I mean is that is t-u or u-t is <=50 and >= -100 should be
immaterial and t-u or u-t is >50 or < -100 is left blank