I want the below formula to return no value at all for a blank space.
at the moment it is returning a 1 for all blank spaces
=IF(AND(X56="S", Y56="Long"), "-1", "1")
Can someone please help me add the rule I cant figure out how to add it in.
In your query, you are doing the English equivalent of:
IF X56="S" AND Y56="Long" THEN -1, ELSE 1
As you can see, an empty value does not fir the rule for -1, then it becomes 1.
There are two ways to solve it. First, you can check for emptyness:
=IF(X56="", "", IF(AND(X56="S", Y56="Long"), "-1", "1") which would translate to if X56 is empty, result is empty. If X56 is not empty, <previous formula>
You could also add the empty condition:
=IF(OR(X56="", Y56="", AND(X56="S", Y56="Long")), "-1", "1")
Which would mean:
If X56 is empty, or Y56 is empty, or (X56=S and Y56=Long both at the same time), then -1; else 1.