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.

 

[Dave Patton]

Did you try post #5?

N.B. The criteria and the formula must be the same either Text or Number.
To coerce Text data to number, we can use +0 or use double negative
We could use a single number or character for criteria or a longer string or larger number; it depends on what we want to secure
We can use Excel's Formula Evaluate to review the components of the formula and the result as it processes sub parts of the formula.

Aggregate.xlsm
	A	B	C	D	E	F	G
1	Category	Value		Character	Max	Min	Structure the formula as you prefer
2	66 aaa	35		66	55	39	Criteria as numbers. The left 3 is coerced to yield numbers like 66
3	66 aaa	39		66	55	35	Criteria as text. In D3, there is a space character after 66; D3 is a text cell.
4	66 aaa	40
5	66 aaa	55					other example
6	366-1 aaa	75		36	77	75	Cell D6 is text criteria is 2 character
7	366-2 aaa	77		36	77	75	The double negative before the Left coerces result to number to match number in D7
8	366-3 aaa	76		566	30	5	The left 3 yields text like "566"
9	566-1 aaa	5		566	30	5	The left 3 is coerced to yield numbers like 566
10	566-2 aaa	10		66	55	35	66 there is a space character after 66; to match use left(D2,2)
11	566-3 aaa	30		66	55	39	The left 2 is coerced to yield numbers like 66
12	566-4 aaa	22
13	566-5 aaa	6
14
2d

Cell Formulas
Range	Formula
E2,E9	E2	=AGGREGATE(14,6,$B$2:$B$13/(--LEFT($A$2:$A$13,3)=D2),1)
F2,F9	F2	=AGGREGATE(15,6,$B$3:$B$14/(--LEFT($A$3:$A$14,3)=D2),1)
E3,E8	E3	=AGGREGATE(14,6,$B$2:$B$13/(LEFT($A$2:$A$13,3)=D3),1)
F3	F3	=AGGREGATE(15,6,$B$2:$B$13/(LEFT($A$2:$A$13,3)=D3),1)
E6	E6	=AGGREGATE(14,6,$B$2:$B$13/(LEFT($A$2:$A$13,2)=D6),1)
F6	F6	=AGGREGATE(15,6,$B$2:$B$13/(LEFT($A$2:$A$13,2)=D6),1)
E7,E11	E7	=AGGREGATE(14,6,$B$2:$B$13/(--LEFT($A$2:$A$13,2)=D7),1)
F7,F11	F7	=AGGREGATE(15,6,$B$3:$B$14/(--LEFT($A$3:$A$14,2)=D7),1)
F8	F8	=AGGREGATE(15,6,B4:B15/(LEFT(A4:A15,3)=D8),1)
E10	E10	=AGGREGATE(14,6,$B$2:$B$13/(LEFT($A$2:$A$13,2)=LEFT(D10,2)),1)
F10	F10	=AGGREGATE(15,6,$B$2:$B$13/(LEFT($A$2:$A$13,2)=LEFT(D10,2)),1)

 

[Dave Patton]

time limit

Aggregate.xlsm
	A	B	C	D	E	F	G
1	Category	Value		Character	Max	Min	Structure the formula as you prefer
2	66 aaa	35		66	55	39	Criteria as numbers. The left 3 is coerced to yield numbers like 966 or 66. We want text that starts with 3 numbers or 2 numbers and a space.
3	66 aaa	39		66	55	35	Criteria as text. In D3, there is a space character after 66; D3 is a text cell.
4	66 aaa	40
5	66 aaa	55					other example
6	366-1 aaa	75		36	77	75	Cell D6 is text criteria is 2 character
7	366-2 aaa	77		36	77	75	The double negative before the Left coerces result to number to match number in D7
8	366-3 aaa	76		566	30	5	The left 3 yields text like "566"
9	566-1 aaa	5		566	30	5	The left 3 is coerced to yield numbers like 566
10	566-2 aaa	10		66	55	35	66 there is a space character after 66; to match use left(D2,2)
11	566-3 aaa	30		66	55	39	The left 2 is coerced to yield numbers like 66
12	566-4 aaa	22
13	566-5 aaa	6
2d

Cell Formulas
Range	Formula
E2,E9	E2	=AGGREGATE(14,6,$B$2:$B$13/(--LEFT($A$2:$A$13,3)=D2),1)
F2,F9	F2	=AGGREGATE(15,6,$B$3:$B$14/(--LEFT($A$3:$A$14,3)=D2),1)
E3,E8	E3	=AGGREGATE(14,6,$B$2:$B$13/(LEFT($A$2:$A$13,3)=D3),1)
F3	F3	=AGGREGATE(15,6,$B$2:$B$13/(LEFT($A$2:$A$13,3)=D3),1)
E6	E6	=AGGREGATE(14,6,$B$2:$B$13/(LEFT($A$2:$A$13,2)=D6),1)
F6	F6	=AGGREGATE(15,6,$B$2:$B$13/(LEFT($A$2:$A$13,2)=D6),1)
E7,E11	E7	=AGGREGATE(14,6,$B$2:$B$13/(--LEFT($A$2:$A$13,2)=D7),1)
F7,F11	F7	=AGGREGATE(15,6,$B$3:$B$14/(--LEFT($A$3:$A$14,2)=D7),1)
F8	F8	=AGGREGATE(15,6,B4:B15/(LEFT(A4:A15,3)=D8),1)
E10	E10	=AGGREGATE(14,6,$B$2:$B$13/(LEFT($A$2:$A$13,2)=LEFT(D10,2)),1)
F10	F10	=AGGREGATE(15,6,$B$2:$B$13/(LEFT($A$2:$A$13,2)=LEFT(D10,2)),1)

 

[MikeyP14]

Hi Dave,

Yes I did try post number 5 and it gave me an error when I tried it and I am not sure why. It seems that you and Fluff are both approaching the problem in the same manner and it seems that the aggregate function is very useful for this type of manipulation. I need to do some more studying and increase my understanding of this function so I can better utilize it in the future. I do see in your latest proposed solution it appears that the Aggregate function is utilized differently depending on if the data is a number or text. What I like about the other solution provided by Fluff is that it seems it can be utilized with numbers and text and can evaluate them synonymously eliminating the need to use different formulas.

Thanks for your explanation of each one of your answers that is a great education lesson for me.

Best Regards,

M

 

[Dave Patton]

Thanks for the feedback.

I find it very useful to step through the examples to see why they work or why they do not work.
Excel's Formulas Formula Evaluate helps.
N.B. True evaluates to 1 and False evaluates to 0