gtd526
Well-known Member
- Joined
- Jul 30, 2013
- Messages
- 660
- Office Version
- 2019
- Platform
- Windows
Hello,
Need to calculate G/L$ (F:F), per symbol, per user, AMT% (D:D)
Top Half = Symbol, Gain/Loss (I:I)
Bottom Half = User, Symbol, Amt% (D:D), G/L$ (needed)
thank you.
Need to calculate G/L$ (F:F), per symbol, per user, AMT% (D:D)
Top Half = Symbol, Gain/Loss (I:I)
Bottom Half = User, Symbol, Amt% (D:D), G/L$ (needed)
thank you.
| The Whole Enchilada.xlsm | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 8 | Symbol | Shares | Cost | CURRENT | MAX Value | MIN Value | Cost Value | Market Value | Gain/Loss | G/L % | ||
| 9 | TRIRX | 25 | $ 20.00 | $ 25.00 | $ 25.00 | $ 20.00 | $ 500.00 | $ 625.00 | $ 125.00 | 25.00% | ||
| 10 | AAPL | 10 | $ 12.00 | $ 14.00 | $ 14.00 | $ 14.00 | $ 120.00 | $ 140.00 | $ 20.00 | 16.67% | ||
| 11 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | |||||
| 12 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | |||||
| 13 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | |||||
| 14 | $ 620.00 | $ 765.00 | $ 145.00 | 23.39% | ||||||||
| 15 | ||||||||||||
| 16 | User | Symbol | Amt $ | Amt % | Total $ | G/L$ | G/L% | OutPut $ | ||||
| 17 | User 1 | TRIRX | $ 500.00 | 75% | $ 375.00 | need | ||||||
| 18 | User 2 | TRIRX | $ 500.00 | 25% | $ 125.00 | need | ||||||
| 19 | User 1 | AAPL | $ 120.00 | 65% | $ 78.00 | need | ||||||
| 20 | User 2 | AAPL | $ 120.00 | 35% | $ 42.00 | need | ||||||
Ameritrade | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E9:E13 | E9 | =IF($D9=0,0,MAX($D9,$E9)) |
| F9:F13 | F9 | =IF($F9=0,$D9,MEDIAN($F9,$D9,0)) |
| G9:G13 | G9 | =$B9*$C9 |
| H9:H13 | H9 | =$B9*$D9 |
| I9:I13 | I9 | =H9-G9 |
| J9:J14 | J9 | =IFERROR((I9/G9),"") |
| G14:I14 | G14 | =SUM(G9:G13) |
| E17:E20 | E17 | =C17*D17 |
