Min/Max value for certain value

[gtd526]

Hello,
Looking for the min/max value in a string of cells in a row.

if "TECL" max range = U4:ZZ4
if "SOXL" max range = U5:ZZ5
if "FNGG" max range = U6:ZZ6
these values could change rows due to sorting by G/L$ value.
X12:AC12 = your test range

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
2	SCHWAB														# of	Short vs				Sold
3	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
4	TECL	Tech 3x	Long	45	$24.10	$46.50	#N/A	#N/A	$1,084.50	$2,092.50	$1,008.00	92.95%	#DIV/0!	9/23/22	386	Long	0%	$0.00	$1,008.00	open	61.16%	71.43%	94.11%	82.64%	69.82%	56.81%	54.83%	66.55%	92.95%	92.95%
5	SOXL	Semis 3x	Long	98	$20.40	$18.98	#N/A	#N/A	$1,999.20	$1,860.04	-$139.16	-6.96%	#DIV/0!	10/10/23	4	Short	10%	$0.00	-$139.16	open									-6.96%	-6.96%
6	FNGG	FANGs 2x	Long	27	$77.07	$71.14	#N/A	#N/A	$2,080.89	$1,920.78	-$160.11	-7.69%	#DIV/0!	7/25/23	81	Short	10%	$0.00	-$160.11	open	-15.26%	-10.82%	-0.17%	-1.63%	-3.92%	-13.04%	-12.14%	-6.11%	-7.69%	-7.69%
7
8
9
10
11				170 shares					$5,164.59	$5,873.32	$708.73	13.72%	#DIV/0!					$0.00	$708.73					Max	Min	Max	Min	Max	Min
12																							????							????
13																								TECL		FNGG		SOXL
Sheet1

Cell Formulas
Range	Formula
F10,F4:F6	F4	=IF(ISTEXT($A4),VLOOKUP($A4,'All Tickers'!$I$1:$J$200,2,0),"")
G10,G4:G6	G4	=IF(ISBLANK($A4),"",IF($F4=0,0,MAX($F4,$G4)))
H10,H4:H6	H4	=IF(ISBLANK($A4),"",IF($H4=0,$F4,MEDIAN($H4,$F4,0)))
I10,I4:I6	I4	=IF($A4="","",D4*E4)
J10,J4:J6	J4	=IF($A4="","",$D4*$F4)
K10,K4:K6	K4	=IF($A4="","",J4-I4)
L4	L4	=IF($A4="","",($J4-$I4)/$I4)
M10,M4:M6	M4	=IF($A4="","",J4/$J$16)
L10,L5:L6	L5	=IF($A5="","",K5/I5)
O10,O4:O6	O4	=IF(A4="","",TODAY()-N4)
P10,P4:P6	P4	=IF(ISNUMBER($N4),IF(DATEDIF($N4,TODAY(),"d")<365,"Short","Long"),"")
Q10,Q4:Q6	Q4	=IF(ISTEXT($A4),IF(TODAY()-N4>365,0,0.1),"")
R10,R4:R6	R4	=IF(ISTEXT($A4),IF(K4>0,K4*Q4,0),"")
S10,S4:S6	S4	=IF(ISTEXT($A4),IF($K4=0,"$0",$K4-$R4),"")
B10,B4:B6	B4	=IF(COUNTIF(Sectors!$A$2:$ABW$47,$A4)=1,INDEX(Sectors!$A$2:$ABW$2,MAX((Sectors!$A$2:$ABW$47=$A4)*(COLUMN(Sectors!$A$2:$ABW$2)))),"")
AD3	AD3	=AC3+7
AD4:AD6	AD4	=$L4
I11	I11	=SUM($I$9:$I$15)
J11	J11	=SUM($J$9:$J$15)
K11	K11	=SUM($K$9:$K$15)
L11	L11	=$K$11/$I$11
M11,R11:S11	M11	=SUM(M4:M10)
D11	D11	=SUM(D4:D10) & " shares"
O11	O11	=IF(ISNUMBER($N11),K11-N11,"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[StephenCrump]

Along these lines, perhaps?

	A	B	C	D	E
1	TECL	1	2	3	6
2	SOXL	7	8	1	2
3	FNGG	15	12	11	10
4
5		Results
6		SOXL	8
7		TECL	6
8		X	#N/A
Sheet1

Cell Formulas
Range	Formula
C6:C8	C6	=MAX(INDEX($B$1:$E$3,MATCH(B6,$A$1:$A$3,),))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[gtd526]

Along these lines, perhaps?

	A	B	C	D	E
1	TECL	1	2	3	6
2	SOXL	7	8	1	2
3	FNGG	15	12	11	10
4
5		Results
6		SOXL	8
7		TECL	6
8		X	#N/A
Sheet1

Cell Formulas
Range	Formula
C6:C8	C6	=MAX(INDEX($B$1:$E$3,MATCH(B6,$A$1:$A$3,),))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you!!

 

[Peter_SSs]

Along these lines, perhaps?

Not sure, but does that need array entry?

FWIW another option for post 2 layout:

=AGGREGATE(14,6,B$1:E$3/(A$1:A$3=B6),1)