Jun 05 2021 07:37 PM
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 | $ - |
Jun 06 2021 11:25 AM
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, ""
))
Jun 07 2021 06:57 PM