2

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?

  • I don't understand these values. What is the unit of "software"? – Wutnaut May 20 '14 at 20:40
  • Its a name, but conventionally created in such a way that by sorting alphanumerically, the latest version should be at the bottom. – Carly May 21 '14 at 16:26
2

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.

| improve this answer | |
  • Just a quick clarification, I need this to first match the product family in a different sheet, and then return the minimum software version (so I'll need two different ranges...first to match product family, second to refer to the software column). Which range do I put where? – Carly May 21 '14 at 16:30
  • Use this formula wherever you think your MIN would go. – benshepherd May 21 '14 at 18:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.