gtd526
Well-known Member
- Joined
- Jul 30, 2013
- Messages
- 657
- Office Version
- 2019
- Platform
- Windows
Hello,
Looking to find the last cell value in a Row for a specific lookup value (A9:A15). The Rows of the lookup value will change due to sorting by G/L.
ie Last cell value for TECL, FNGG, SOXL to find the last cell value in a Row. If I could extend the range to U9:ZZ15, for an unlimited range number to find the last value.
Thank you.
Looking to find the last cell value in a Row for a specific lookup value (A9:A15). The Rows of the lookup value will change due to sorting by G/L.
ie Last cell value for TECL, FNGG, SOXL to find the last cell value in a Row. If I could extend the range to U9:ZZ15, for an unlimited range number to find the last value.
Thank you.
| The Whole Enchilada.xlsm | ||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | |||
| 7 | SCHWAB | # of | Short vs | Sold | ||||||||||||||||||||||||||||||
| 8 | Symbol | Sector | Type | Shares | Price | CURRENT | MAX Value | MIN Value | Cost | Balance | G/L $ | G/L % | Weight(%) | Buy Date | Days | Long Term | Cap Gain % | Cap Ga Cost | Adj Gain | Status | 8/19/2023 | 8/26/2023 | 9/2/2023 | 9/9/2023 | 9/16/2023 | 9/23/2023 | 9/30/2023 | 10/7/2023 | 10/14/2023 | 10/21/2023 | ||||
| 9 | TECL | Tech 3x | Long | 45 | $24.10 | $47.02 | #N/A | #N/A | $1,084.50 | $2,115.90 | $1,031.40 | 95.10% | #DIV/0! | 9/23/22 | 388 | Long | 0% | $0.00 | $1,031.40 | open | 61.16% | 71.43% | 94.11% | 82.64% | 69.82% | 56.81% | 54.83% | 66.55% | 92.95% | 95.10% | ||||
| 10 | SOXL | Semis 3x | Long | 98 | $20.40 | $19.40 | #N/A | #N/A | $1,999.20 | $1,901.20 | -$98.00 | -4.90% | #DIV/0! | 10/10/23 | 6 | Short | 10% | $0.00 | -$98.00 | open | -6.96% | -4.90% | ||||||||||||
| 11 | FNGG | FANGs 2x | Long | 27 | $77.07 | $71.89 | #N/A | #N/A | $2,080.89 | $1,941.03 | -$139.86 | -6.72% | #DIV/0! | 7/25/23 | 83 | Short | 10% | $0.00 | -$139.86 | open | -15.26% | -10.82% | -0.17% | -1.63% | -3.92% | -13.04% | -12.14% | -6.11% | -7.69% | -6.72% | ||||
| 12 | ||||||||||||||||||||||||||||||||||
| 13 | ||||||||||||||||||||||||||||||||||
| 14 | ||||||||||||||||||||||||||||||||||
| 15 | ||||||||||||||||||||||||||||||||||
| 16 | ||||||||||||||||||||||||||||||||||
| 17 | ??? | ??? | ??? | |||||||||||||||||||||||||||||||
| 18 | Max | Min | First | Last | Max | Min | First | Last | Max | Min | First | Last | ||||||||||||||||||||||
| 19 | 95.10% | 54.83% | 61.16% | ??? | -0.17% | -15.26% | -15.26% | ??? | -4.90% | -6.96% | -6.96% | ??? | ||||||||||||||||||||||
| 20 | TECL | TECL | FNGG | FNGG | SOXL | SOXL | ||||||||||||||||||||||||||||
| 21 | #VALUE! | #VALUE! | #VALUE! | |||||||||||||||||||||||||||||||
| 22 | Weekly Diff | Weekly Diff | Weekly Diff | |||||||||||||||||||||||||||||||
| 23 | 2.16% | 0.97% | 2.06% | |||||||||||||||||||||||||||||||
Sheet1 | ||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F15,F9:F11 | F9 | =IF(ISTEXT($A9),VLOOKUP($A9,'All Tickers'!$I$1:$J$202,2,0),"") |
| G15,G9:G11 | G9 | =IF(ISBLANK($A9),"",IF($F9=0,0,MAX($F9,$G9))) |
| H15,H9:H11 | H9 | =IF(ISBLANK($A9),"",IF($H9=0,$F9,MEDIAN($H9,$F9,0))) |
| I15,I9:I11 | I9 | =IF($A9="","",D9*E9) |
| J15,J9:J11 | J9 | =IF($A9="","",$D9*$F9) |
| K15,K9:K11 | K9 | =IF($A9="","",J9-I9) |
| L9 | L9 | =IF($A9="","",($J9-$I9)/$I9) |
| M15,M9:M11 | M9 | =IF($A9="","",J9/$J$16) |
| L15,L10:L11 | L10 | =IF($A10="","",K10/I10) |
| O15,O9:O11 | O9 | =IF(A9="","",TODAY()-N9) |
| P15,P9:P11 | P9 | =IF(ISNUMBER($N9),IF(DATEDIF($N9,TODAY(),"d")<365,"Short","Long"),"") |
| Q15,Q9:Q11 | Q9 | =IF(ISTEXT($A9),IF(TODAY()-N9>365,0,0.1),"") |
| R15,R9:R11 | R9 | =IF(ISTEXT($A9),IF(K9>0,K9*Q9,0),"") |
| S15,S9:S11 | S9 | =IF(ISTEXT($A9),IF($K9=0,"$0",$K9-$R9),"") |
| B15,B9:B11 | B9 | =IF(COUNTIF(Sectors!$A$2:$ABW$47,$A9)=1,INDEX(Sectors!$A$2:$ABW$2,MAX((Sectors!$A$2:$ABW$47=$A9)*(COLUMN(Sectors!$A$2:$ABW$2)))),"") |
| AD8 | AD8 | =AC8+7 |
| AD9:AD11 | AD9 | =$L9 |
| U19,AC19,Y19 | U19 | =MAX(INDEX($U$9:$ZZ$15,MATCH(U20,$A$9:$A$15,),)) |
| V19,AD19,Z19 | V19 | =MIN(INDEX($U$9:$ZZ$15,MATCH(U20,$A$9:$A$15,),)) |
| W19,AA19 | W19 | =INDEX($U$9:$U$15,MATCH(U20,$A$9:$A$15,0)) |
| W21,AE21,AA21 | W21 | =X19-W19 |
| W23 | W23 | =LOOKUP(2, 1/($A$9:$ZX$9<>""), $A$9:$ZX$9)-INDEX(9:9, AGGREGATE(14, 6, COLUMN(A:ZX)/(A9:ZX9<>""), 2)) |
| AA23 | AA23 | =LOOKUP(2, 1/($A$11:$ZX$11<>""), $A$11:$ZX$11)-INDEX(11:11, AGGREGATE(14, 6, COLUMN(A:ZX)/(A11:ZX11<>""), 2)) |
| AE23 | AE23 | =LOOKUP(2, 1/($A$10:$ZX$10<>""), $A$10:$ZX$10)-INDEX(10:10, AGGREGATE(14, 6, COLUMN(C:ZZ)/(C10:ZZ10<>""), 2)) |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
