Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with formula
=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 -- Libby |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with formula
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 Replace @mailinator.com with @tiscali.co.uk "Libby" wrote in message ... =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 -- Libby |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with formula
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","")) -- Regards, Peo Sjoblom "Libby" wrote in message ... =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 -- Libby |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with formula
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 -- Libby "Libby" wrote: =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 -- Libby |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with formula
=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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with formula
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 -- Libby "Rick Rothstein (MVP - VB)" wrote: =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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula problem | Excel Discussion (Misc queries) | |||
Formula Problem | Excel Worksheet Functions | |||
formula problem | Excel Worksheet Functions | |||
problem with formula | Excel Discussion (Misc queries) | |||
Problem with formula IF(C5="", "",NOW()) | Excel Discussion (Misc queries) |