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

%3CLINGO-SUB%20id%3D%22lingo-sub-2419369%22%20slang%3D%22en-US%22%3EFORMULA%20TO%20MATCH%20SHORT%20OR%20LONG%20STOCK%20PURCH%20PRICE%20TO%20CURRENT%20PRICE%20-GAIN%2FLOSS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2419369%22%20slang%3D%22en-US%22%3E%3CP%3ETRYING%20TO%20DEVELOP%20FORMULA%20THAT%20WILL%20TELL%20WHETHER%20I%20HAVE%20A%20GAIN%20OR%20LOSS%20ON%20A%20TRANSACTION%26nbsp%3B%20IN%20EACH%20ROW%20AND%20IT%20WILL%20POSSIBLY%20VARY%20BET%20SEQUENTIAL%20ROWS%26nbsp%3B%20as%20to%20short%20%22%20S%22%20and%20long%20%22L%22AND%20IF%20IT%20IS%20A%20SHORT%20POSITION%2C%20THERE%20IS%20A%20GAIN%20IF%20THE%20%22now%20underlier%20bid%22%20IS%20LESS%20THAN%20THE%20%22ul-open-ask%22%20and%20if%20the%20%22now%22%20is%20greater%20than%20the%20%22ul%22%20then%20there%20is%20a%20loss%3C%2FP%3E%3CP%3E-with%20a%20long%20position%20the%20rule%20is%20opposite%3C%2FP%3E%3CP%3E-i%20set%20up%20a%20column%20of%20%22L%22%20for%26nbsp%3B%20LONG%20POSITION%20AND%20%22S%22%20FOR%20SHORT%20POSITION%20AND%20THAT%20IS%20THE%20STARTING%26nbsp%3B%20test%20POINT%20FOR%20MY%20ABOVE%20FORMULA%20WHICH%20GIVES%20ME%20A%20%22%23VALUE%22%20or%20%22%23NAME%22%20ANSWER%20AND%20I%20CANNOT%20GET%20IT%20TO%20TEST%20FOR%20%22S%22%20OR%20%22L%22%20AND%20THEN%20TEST%20THE%20PRICE%20AGAINST%20THE%20OPEN%20PRICE%20AND%20THE%20CURRENT%20PRICE%20TO%20SEE%20IF%20THERE%20IS%20A%20PROFIT%20OR%20LOSS%20AND%20PLACE%20THAT%20PROFIT%20OR%20LOSS%20FIGURE%20IN%20COLUMN%20H%20AS%20A%20NEGATIVE%20OR%20A%20POSITIVE%20IT%20LOOKS%20TO%20ME%20TO%20BE%20A%20MULTI%20STEP%20TEST-%201.)%20IS%20IT%20%22S%22%20AND%20IF%20SO%20IS%20IT%20A%20MINUS%20%24%24%20AMT%20OR%20A%20NEGATIVE%20AMT%20AND%20IF%20IT%20IS%20NOT%20AN%20%22S%22%20THEN%20IT%20HAS%20TO%20BE%20AN%20%22L%22%20AND%20THE%20OPPOSITE%20OF%20THE%20%22S%22%20COMPUTATIONS%20HAVE%20TO%20OCCUR%3C%2FP%3E%3CP%3E-THE%20FIRST%20HALF%20OF%20THE%20FORMULA%20IS%20GOOD%20FOR%20%22S%22%20BUT%20WHEN%20I%20TRY%20TO%20ADD%20A%20SECOND%20LEG%26nbsp%3B%20OF%20%22IF%20AND%20FOR%20THE%20%22L%22%20TEST%20I%20GET%20%23VALUE%26nbsp%3B%20OR%20%23NAME%20AND%20CANNOT%20GET%20PAST%20THAT%26nbsp%3B%3C%2FP%3E%3CP%3E-THE%20FORMULA%20WONT%20ACCEPT%20THE%20SECOND%20LEG%3C%2FP%3E%3CP%3E-%20I%20am%20afraid%20it%20is%20something%20simple%20like%20the%20connection%20between%20the%20first%20and%20second%20test%20but%20i%20cannot%20get%20there%3C%2FP%3E%3CP%3E-THANK%20YOU%20FOR%20THE%20HELP%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(I19%3D%22S%22%2CG19%26gt%3BF19)%2CF19-G19%2CF19-G19)%2CIF(AND(I19%3D%22L%22%2CF19%26gt%3BG19)%2CF19-G19%2CF19-G19)%3C%2FP%3E%3CTABLE%20width%3D%22803%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2239%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2258%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2295%22%3EC%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22120%22%3EE%3C%2FTD%3E%3CTD%20width%3D%22116%22%3EF%3C%2FTD%3E%3CTD%20width%3D%22127%22%3EG%3C%2FTD%3E%3CTD%20width%3D%22107%22%3EH%3C%2FTD%3E%3CTD%20width%3D%2261%22%3EI%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EL-sb%3C%2FTD%3E%3CTD%3ES-sb%3C%2FTD%3E%3CTD%3EL-tdam%3C%2FTD%3E%3CTD%3ES-tdam%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EDATA%20CLEAR%20MACRO%20%22ctl%20c%22%26gt%3B%26gt%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ECLICK%20HERE%3C%2FTD%3E%3CTD%3Ewarning%20UNPROTECTED%20SHEET%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ETODAY%26gt%3B%26gt%3B%3C%2FTD%3E%3CTD%3E6%2F5%2F2021%3C%2FTD%3E%3CTD%3ENOW%20TIME%26gt%3B%26gt%3B%3C%2FTD%3E%3CTD%3E9%3A43%20PM%3C%2FTD%3E%3CTD%3E5.61%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ESOLD%3C%2FTD%3E%3CTD%3EPURCDATE%3C%2FTD%3E%3CTD%3EBUY%20TIME%3C%2FTD%3E%3CTD%3EUL-SYM%3C%2FTD%3E%3CTD%3EUL-OPEN-ASK%3C%2FTD%3E%3CTD%3ENOW-UL-BID%3C%2FTD%3E%3CTD%3E%26nbsp%3BNOW%24%24DIFF%3C%2FTD%3E%3CTD%3EL%20%2FS%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E12%2F21%2F20%3C%2FTD%3E%3CTD%3E3%3AO5PM%3C%2FTD%3E%3CTD%3EADAP%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%204.78%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%205.03%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200.25%3C%2FTD%3E%3CTD%3E%26nbsp%3BL%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E5%2F24%2F21%3C%2FTD%3E%3CTD%3E9%3A48am%3C%2FTD%3E%3CTD%3EADMA%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201.74%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201.75%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200.01%3C%2FTD%3E%3CTD%3E%26nbsp%3BL%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E5%2F24%2F21%3C%2FTD%3E%3CTD%3E9%3A55am%3C%2FTD%3E%3CTD%3EACRX%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201.29%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201.45%3C%2FTD%3E%3CTD%3E%23NAME%3F%3C%2FTD%3E%3CTD%3E%26nbsp%3BL%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E5%2F24%2F21%3C%2FTD%3E%3CTD%3E10%3A15am%3C%2FTD%3E%3CTD%3EABEV%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%203.32%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%203.00%3C%2FTD%3E%3CTD%3E%23VALUE!%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2419369%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2420200%22%20slang%3D%22en-US%22%3ERe%3A%20FORMULA%20TO%20MATCH%20SHORT%20OR%20LONG%20STOCK%20PURCH%20PRICE%20TO%20CURRENT%20PRICE%20-GAIN%2FLOSS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2420200%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F164676%22%20target%3D%22_blank%22%3E%40william%20palmer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20I%20skipped%20major%20past%20of%20the%20post%2C%20it's%20practically%20impossible%20to%20read%20the%20text%20in%20All%20Caps.%20As%20I%20understood%20the%20question%20is%20in%20this%20errors%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20259px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F286665i3A4BB06CFEDD23D9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ereturned%20by%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(AND(I19%3D%22S%22%2CG19%26gt%3BF19)%2C%0A%20%20%20%20F19-G19%2C%0A%20%20%20%20F19-G19)%2C%0A%20IF(AND(I19%3D%22L%22%2CF19%26gt%3BG19)%2C%0A%20%20%20%20F19-G19%2C%0A%20%20%20%20F19-G19%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EFormula%20itself%20is%20incorrect%2C%20perhaps%20you%20mean%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(AND(I19%3D%22S%22%2CG19%26gt%3BF19)%2C%0A%20%20%20%20G19-F19%2C%0A%20IF(AND(I19%3D%22L%22%2CF19%26gt%3BG19)%2C%0A%20%20%20%20F19-G19%2C%20%22%22%0A))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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)

ABC EFGHI
  L-sbS-sbL-tdamS-tdam   
   DATA CLEAR MACRO "ctl c">> CLICK HEREwarning UNPROTECTED SHEET
   TODAY>>6/5/2021NOW TIME>>9:43 PM5.61 
 SOLDPURCDATEBUY TIMEUL-SYMUL-OPEN-ASKNOW-UL-BID NOW$$DIFFL /S
1 12/21/203:O5PMADAP $              4.78 $            5.03 $           0.25 L 
3 5/24/219:48amADMA $              1.74 $            1.75 $           0.01 L 
4 5/24/219:55amACRX $              1.29 $            1.45#NAME? L 
5 5/24/2110:15amABEV $              3.32 $            3.00#VALUE!
6       $                 -   
2 Replies

@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

image.png

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