Jun 28 2019 03:47 PM
HI - Trying to write a statement that will do the following
If "Closed" then return 0
If "P" AND X < Y, then Y-X, otherwise 0
If "C" AND X>=Y, then X-Y, otherwise 0
Below is the statement I wrote and seems like it works if I have "Closed" or "C" but not "P"
=IF(AND(A1="Closed",0),IF(AND(H1="P",N1<O1),O1-N1,0),IF(AND(H1="C",N1>=Y1),N1-O1,0))
Any suggestions? Thanks
Jun 28 2019 04:40 PM
Letting 'State' be ["Open"/"Closed"] and 'PC' be ["P","C"]
= (State="Open") * ( (PC="P")*(Y>X)*(Y-X) + (PC="C")*(X>Y)*(X-Y) )
which seems to reduce to
= (State="Open") * ( 1 - (PC="P") - (Y>X) ) * (X-Y)
Jun 28 2019 09:47 PM
Jun 29 2019 09:54 AM
SolutionJul 02 2019 04:49 PM
Sergei - thank you for your super fast response and assistance with my Excel queries
Jun 29 2019 09:54 AM
SolutionOne more variant
=(A1<>"Closed")*(X1-Y1)*CHOOSE((X1<Y1)+1,(H1="C"),-(H1="P"))