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

Occasional Contributor

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

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 Replies

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

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, ""
))``````

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

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