FORMULA TO MATCH SHORT OR LONG STOCK PURCH PRICE TO CURRENT PRICE -GAIN/LOSS

[william palmer]

TRYING TO DEVELOP FORMULA THAT WILL TELL WHETHER I HAVE A GAIN OR LOSS ON A TRANSACTION  IN EACH ROW AND IT WILL POSSIBLY VARY BET SEQUENTIAL ROWS  as to short " S" and long "L"AND IF IT IS A SHORT POSITION, THERE IS A GAIN IF THE "now underlier bid" IS LESS THAN THE "ul-open-ask" and if the "now" is greater than the "ul" then there is a loss

-with a long position the rule is opposite

-i set up a column of "L" for  LONG POSITION AND "S" FOR SHORT POSITION AND THAT IS THE STARTING  test POINT FOR MY ABOVE FORMULA WHICH GIVES ME A "#VALUE" or "#NAME" ANSWER AND I CANNOT GET IT TO TEST FOR "S" OR "L" AND THEN TEST THE PRICE AGAINST THE OPEN PRICE AND THE CURRENT PRICE TO SEE IF THERE IS A PROFIT OR LOSS AND PLACE THAT PROFIT OR LOSS FIGURE IN COLUMN H AS A NEGATIVE OR A POSITIVE IT LOOKS TO ME TO BE A MULTI STEP TEST- 1.) IS IT "S" AND IF SO IS IT A MINUS $$ AMT OR A NEGATIVE AMT AND IF IT IS NOT AN "S" THEN IT HAS TO BE AN "L" AND THE OPPOSITE OF THE "S" COMPUTATIONS HAVE TO OCCUR

-THE FIRST HALF OF THE FORMULA IS GOOD FOR "S" BUT WHEN I TRY TO ADD A SECOND LEG  OF "IF AND FOR THE "L" TEST I GET #VALUE  OR #NAME AND CANNOT GET PAST THAT 

-THE FORMULA WONT ACCEPT THE SECOND LEG

- I am afraid it is something simple like the connection between the first and second test but i cannot get there

-THANK YOU FOR THE HELP 

=IF(AND(I19="S",G19>F19),F19-G19,F19-G19),IF(AND(I19="L",F19>G19),F19-G19,F19-G19)

A	B	C		E	F	G	H	I
		L-sb	S-sb	L-tdam	S-tdam
			DATA CLEAR MACRO "ctl c">>		CLICK HERE	warning UNPROTECTED SHEET
			TODAY>>	6/5/2021	NOW TIME>>	9:43 PM	5.61
	SOLD	PURCDATE	BUY TIME	UL-SYM	UL-OPEN-ASK	NOW-UL-BID	NOW$$DIFF	L /S
1		12/21/20	3:O5PM	ADAP	$              4.78	$            5.03	$           0.25	L
3		5/24/21	9:48am	ADMA	$              1.74	$            1.75	$           0.01	L
4		5/24/21	9:55am	ACRX	$              1.29	$            1.45	#NAME?	L
5		5/24/21	10:15am	ABEV	$              3.32	$            3.00	#VALUE!
6							$                 -

[#2]

@william palmer 

Sorry, I skipped major past of the post, it's practically impossible to read the text in All Caps. As I understood the question is in this errors

returned by

=IF(AND(I19="S",G19>F19),
    F19-G19,
    F19-G19),
 IF(AND(I19="L",F19>G19),
    F19-G19,
    F19-G19
)

Formula itself is incorrect, perhaps you mean

=IF(AND(I19="S",G19>F19),
    G19-F19,
 IF(AND(I19="L",F19>G19),
    F19-G19, ""
))

[#3]

Sir - thank You for your effort in trying to decipher my problem presented all in caps.
-I feel ill at ease in operating and presenting in this environment, never having done so
-This testing that I am trying to accomplish, involves whether there is a short position, "S"For a long position, "L" in the column entitled "L/S". Unfortunately, the "S" did not show in the blanks cell To the right of the point where your red marker stopped. The pricing computation for a profit or loss in a short position is inapposite that the pricing computation for profit in a long position. When I use the 1st half of the formula with an =IF(AND) for the short position "S" and there is an "S" for that test in the "L/S" column the computation is accurate. I do not know how familiar you are with the computation for profit in a long position versus a short position. In a short position if the opening price is $3 and the current price is $4 . There is a loss and I have to subtract the $4 from the $3 to get the $1 negative (loss). On the other hand, if the current price is $2. I have a $1 gain by again subtracting the $2 from $3. This protocol works satisfactorily until I try to add the "IF(AND For a long position ("L") and when I do that I get an error in the "NOW$$DIFF" column. In a long position the computations for profit and loss are exactly opposite a short position. I cannot understand where my coating is bad when I try and join the 2 concepts, each performing 3 separate tests for profit and loss. If you have some wisdom I would greatly appreciate it because I am totally stumped-thanks Bill Palmer