QUESTION :
I need to come up with a formula in excel that will identify the max and min software offered on a computer and input it into a new table.
So for instance, in one large table I have column names like SKU, PRICE, PRODUCT FAMILY, SOFTWARE VERSION, etc.
In another table I have a list of PRODUCT FAMILY, and I need to identify the max/min software.
Software can be sorted in excel:
15.0(1)M2
15.0(1)M4
15.1(2)T1
15.1(2)T2
15.1(4)M3
15.1(4)M4
15.2(4)M3
But I cannot figure out how to return these as max/min values. Attempted Array Formula:
=MIN(IF(A4=Sheet1!$V$3:$V$644, Sheet1!$AE$3:$AE$644))
But this seems to be struggling with the non-numerical names. Does anyone have suggestions?
ANSWER :
MIN only works on numerical values. You have to be a bit cleverer. I stole this formula from the PC Review forum:
=INDEX(range,MATCH(0,COUNTIF(range,"<"&range),0))
where range refers to your Software Version column. It doesn’t seem to work if you specify a whole column (e.g. A:A).
It’s an array formula, so Ctrl-Shift-Enter it of course.