Trying to use is number function in excel to find min and max value of data

[MikeyP14]

Hi All,

I am new to the site but I am trying to find a min and a max value in excel and have a formula that seems to work 90% of the time but if a text string has the number I am trying to filter by in it then it could potentially return the wrong value. See my example table below.

Character		Max	min
66​		77​	5​
Dataset	value
66 aaa	35​
66 aaa	39​
366 aaa	75​
366 aaa	77​
566 aaa	5​
566 aaa	10​

I am able to search the data above using the following criteria
Max (Returns 77 not 39) - =MAX(IF(ISNUMBER(SEARCH(A2,$A3:$A100)),$B3:$B100, ""))
Min (Returns 5 not 35)- =MIN(IF(ISNUMBER(SEARCH(A2,$A3:$A100)),$B3:$B100, ""))


Any guidance you can provide would be greatly appreciated.

 

[mart37]

You can't use a matrix in: SEARCH(A2,$A3:$A100)
You can use a matrix-formula:

Map3
	E
3	39
Blad1

Cell Formulas
Range	Formula
E3	E3	=MAX(((SEARCH(A2,A3:A5,1)=1)*(B3:B5)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jtakw]

Hi,

This is one way:

Book3.xlsx
	A	B	C	D
2	66		39	35
3	Dataset	value
4	66 aaa	35
5	66 aaa	39
6	366 aaa	75
7	366 aaa	77
8	566 aaa	5
9	566 aaa	10
Sheet721

Cell Formulas
Range	Formula
C2	C2	=MAX(IF(ISNUMBER(1/(LEFT($A3:$A100,FIND(" ",$A3:$A100))+0=A2)),$B3:$B100,""))
D2	D2	=MIN(IF(ISNUMBER(1/(LEFT($A3:$A100,FIND(" ",$A3:$A100))+0=A2)),$B3:$B100,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

Note that my formula will Not fail for A2 values that may be Partial value of A3:A100, (e.g. 6, 3, 36, 5, 56, etc.)

Book3.xlsx
	A	B	C	D
2	36		0	0
3	Dataset	value
4	66 aaa	35
5	66 aaa	39
6	366 aaa	75
7	366 aaa	77
8	566 aaa	5
9	566 aaa	10
Sheet721

Cell Formulas
Range	Formula
C2	C2	=MAX(IF(ISNUMBER(1/(LEFT($A3:$A100,FIND(" ",$A3:$A100))+0=A2)),$B3:$B100,""))
D2	D2	=MIN(IF(ISNUMBER(1/(LEFT($A3:$A100,FIND(" ",$A3:$A100))+0=A2)),$B3:$B100,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Dave Patton]

Aggregate.xlsm
	A	B	C	D
1	366 aaa			77
2	Dataset	value
3	66 aaa	35
4	66 aaa	39
5	366 aaa	75
6	366 aaa	77
7	566 aaa	5
8	566 aaa	10
2d

Cell Formulas
Range	Formula
D1	D1	=AGGREGATE(14,6,$B$3:$B$8/(A3:A8=A1),1)

 

[Dave Patton]

for Max or Min by category

Aggregate.xlsm
	A	B	C	D	E	F	G	H
1	366 aaa	3	Max	77	77	Min	75	75
2	Dataset	value
3	66 aaa	35
4	66 aaa	39
5	366 aaa	75
6	366 aaa	77
7	566 aaa	5
8	566 aaa	10
2d

Cell Formulas
Range	Formula
D1	D1	=AGGREGATE(14,6,$B$3:$B$8/(A3:A8=A1),1)
E1	E1	=AGGREGATE(14,6,$B$3:$B$8/(--LEFT(A3:A8,1)=B1),1)
G1	G1	=AGGREGATE(15,6,$B$3:$B$8/(A3:A8=A1),1)
H1	H1	=AGGREGATE(15,6,$B$3:$B$8/(--LEFT(A3:A8,1)=B1),1)

 

[jtakw]

Formulas from post # 3, and added modified versions of your formulas in OP, all formulas will Not fail for partial matches:

Book3.xlsx
	A	B	C	D	E	F
2	66		39	35	39	35
3	Dataset	value
4	66 aaa	35
5	66 aaa	39
6	366 aaa	75
7	366 aaa	77
8	566 aaa	5
9	566 aaa	10
Sheet721

Cell Formulas
Range	Formula
C2	C2	=MAX(IF(ISNUMBER(1/(LEFT($A3:$A100,FIND(" ",$A3:$A100))+0=A2)),$B3:$B100,""))
D2	D2	=MIN(IF(ISNUMBER(1/(LEFT($A3:$A100,FIND(" ",$A3:$A100))+0=A2)),$B3:$B100,""))
E2	E2	=MAX(IF(ISNUMBER(SEARCH(" "&A2&" "," "&$A3:$A100&" ")),$B3:$B100, ""))
F2	F2	=MIN(IF(ISNUMBER(SEARCH(" "&A2&" "," "&$A3:$A100&" ")),$B3:$B100, ""))
Press CTRL+SHIFT+ENTER to enter array formulas.

Book3.xlsx
	A	B	C	D	E	F
2	36		0	0	0	0
3	Dataset	value
4	66 aaa	35
5	66 aaa	39
6	366 aaa	75
7	366 aaa	77
8	566 aaa	5
9	566 aaa	10
Sheet721

Cell Formulas
Range	Formula
C2	C2	=MAX(IF(ISNUMBER(1/(LEFT($A3:$A100,FIND(" ",$A3:$A100))+0=A2)),$B3:$B100,""))
D2	D2	=MIN(IF(ISNUMBER(1/(LEFT($A3:$A100,FIND(" ",$A3:$A100))+0=A2)),$B3:$B100,""))
E2	E2	=MAX(IF(ISNUMBER(SEARCH(" "&A2&" "," "&$A3:$A100&" ")),$B3:$B100, ""))
F2	F2	=MIN(IF(ISNUMBER(SEARCH(" "&A2&" "," "&$A3:$A100&" ")),$B3:$B100, ""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MikeyP14]

Hi All and thanks for the replies I am still getting an error when I am trying to use these new functions but now it is giving me a #value error or a #NUM! error. I do not think I gave enough detail in my first post. My search criteria is always just the base number but sometimes the data has -1 or -2 and is dynamic so it could go up to -20 and so on. My sheet that I am looking at an individual number on is also on a different sheet than the reference data.

It is like the data I have posted below

Sheet 1
Character		Max	min
66		76	6

Max (Returning 76 and not 55) - =MAX(IF(ISNUMBER(SEARCH(A3,Sheet2!A:A)),Sheet2!B:B, ""))
Min (Returning 6 not 35) - =MIN(IF(ISNUMBER(SEARCH(A3,Sheet2!A:A)),Sheet2!B:B,""))

Sheet 2
Dataset	value
66 aaa	35
66 aaa	39
66 aaa	40​
66 aaa	55​
366-1 aaa	75
366-2 aaa	77
366-3 aaa	76​
566-1 aaa	5
566-2 aaa	10
566-3 aaa	30​
566-4 aaa	22​
566-5 aaa	6​

Thanks again for all your help.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D
1	Character		Max	min
2	66		39	35
3
Sheet1

Cell Formulas
Range	Formula
C2	C2	=AGGREGATE(14,6,Sheet2!B2:B13/(LEFT(Sheet2!A2:A13,LEN(A2))=A2&""),1)
D2	D2	=AGGREGATE(15,6,Sheet2!B2:B13/(LEFT(Sheet2!A2:A13,LEN(A2))=A2&""),1)

+Fluff 1.xlsm
	A	B	C
1	Dataset	value
2	66 aaa	35	TRUE
3	66 aaa	39	TRUE
4	66 aaa	40	FALSE
5	66 aaa	55	FALSE
6	366-1 aaa	75	TRUE
7	366-2 aaa	77	TRUE
8	366-3 aaa	76	FALSE
9	566-1 aaa	5	TRUE
10	566-2 aaa	10	TRUE
11	566-3 aaa	30	FALSE
12	566-4 aaa	22	FALSE
13	566-5 aaa	6	FALSE
Sheet2

Cell Formulas
Range	Formula
C2:C13	C2	=ISNUMBER(B2)

Although it looks as though some of your numbers are actually text & not numbers.

 

[MikeyP14]

Thanks All for your help. Fluff that worked perfectly for my dataset thank you! One last question I have is I noticed you used 14 and 15 in the aggregate formula instead of 4 and 5. I went through and modified the code to try and use 4 and 5 simply because they are the max and min callouts but when I did the function broke and gave me an error again. When I changed it back to 14 and 15 being the largest and smallest it worked flawlessly. Is this because you can have additional criteria in the formulas when using 14 and 15?

Just curious as I am not a excel expert but trying to get smarter as I go.

Thanks again!

 

[Fluff]

You can only use arrays in the aggregate function when the 1st argument is 14 or greater.