Apple Numbers, my first use of Sumif...duh
To understand my problem with Apple numbers it would be clearer if you just recreated it. This is for simple stock trading, - (buy low, sell high) = long, (buy high, sell low) = short, both can be profitable, so ($100) in a short is a win.
COLUMNS;
- (Col B) Qty#
- (Col D) SHORT (checkbox) if checked = Short, if not = Long
- (Col E) Entry $price
- (Col F) Exit $price
- (Col K) Long$ (Q3−P3) calculates the win or loss
- (Col L) Short$ (P3−Q3) calculates the win or loss
- (Col P) Invested $Total (E3×ROUND($B3,4))
- (Col Q) Gain/Loss $ (F3×B3)
- (Col K16) Sums column L for the Long trades (SUMIF(D3:D14,"=false",K3:K14)
- (Col L16) Sums column M for the Short trades (SUMIF(D3:D14,"=True",L3:L14)
- (Col M16) adds Long & Short Total (SUM(K16,L16)
WORKFLOW, (Enter these values to easily see problem)
- Stock Ticker (Not Needed)
- Enter Qty (10 for each of the 4 trades)
- Check if SHORT or Unchecked if LONG (Short, Long, Short, Long)
- Enter Entry Price ($100. For each of the 4 trades)
- Enter Exit Price ($90, $110, $90, $110)
CALCULATIONS ARE…
Col K (Long) (Q3−P3)
Col L (Short) (P3−Q3)
Col P ($ Invested Total) (E3×ROUND($B3,4)
Col Q ($ Profit/Loss) (F3×B3)
VALUES I HAVE…
Total for Long K16 should be $200
Total for Short L16 should be $200
Grand Total M16 should be $400.
PROBLEM:
If you have everything the way I have it you should see ($400 Total).
Now All trades are Wins (2 long wins and 2 short wins)
If I accidentally DIDN’T check Col D6 to make it a Short
and then I DID check it turning it into a short, now it shows a
Loss. Notice what happens in the $400 Total.
A correction of a $100 profit (as a Long) now turns into a
$100 Loss (as a Long) on the 4th trade.
This is where I get lost.
$100 profit is actually a $100 loss which is a $200 difference.
BUT the trade ONLY has a $100 Value.
So which is it, and how do I correct it?
Sorry for the long post,
Thank you
I don’t think I can send the spreadsheet it says I need to update OS and Numbers.
Running Ventura 13.6.8 Numbers 14.1 (7040.0.73).
Areas that are greyed out have no relevance to the problem.
iMac 27″
