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