use min function with a criteia

richTheGrill · #1 06-25-2009, 12:16 PM

Hi, I am trying to use the min function using a criteria. I have two columns, one with names and the other with numbers. I want to find the minimum number in column A that relates to a name in column B.

Thanks,
Rich

DonkeyOte · #2 06-25-2009, 12:18 PM

If the data is not sorted then

=MIN(IF(A1:A100="name",B1:B100))
committed with CTRL + SHIFT + ENTER

richTheGrill · #3 06-25-2009, 12:23 PM

Thanks, that is what i was looking for, if you got this twice, sorry, i am new to this site

richTheGrill · #4 06-25-2009, 12:29 PM

Ok, the column is sorted and i am getting back the minimum of the whole column and not the minimum associated with the name, is it because the column is sorted?

DonkeyOte · #5 06-25-2009, 12:33 PM

Was the formulae forced into an Array with CTRL + SHIFT + ENTER (if it was it will appear as {=MIN(...)}, if not, reset the Array.

With the data sorted you may be able to avoid an Array, how is the data sorted - by value or by name - and in which order (asc/desc) ?

richTheGrill · #6 06-25-2009, 12:43 PM

It is sorted by name assending, so when you say force the array you mean to hit CTLR + SHIFT + ENTER instead of just ENTER, right?

DonkeyOte · #7 06-25-2009, 12:45 PM

Yes... CTRL + SHIFT + ENTER

However, if your data is as you say now sorted you can use a non-array approach thanks to the fact that you can use MATCH (with INDEX) to establish the appropriate range to look in (as the data is now effectively grouped together), ie:

=MIN(INDEX(B1:B100,MATCH("name",A1:A100,0)):INDEX(B1:B100,MATCH("name",A1:A100)))

("name" can obviously be a cell containing the name of interest)

richTheGrill · #8 06-25-2009, 12:57 PM

Excellent, that worked on the first name i tried. I had thought it would be an easier formula. I am pretty good with Excel, but i haven't seen a formula like this one, I am curious why the ",0" in the first expresion that "name" was used?

This is a very helpful site by the way!!!

DonkeyOte · #9 06-25-2009, 01:02 PM

Might sound obvious but first point of reference: XL Help... it's useful in understanding the various arguments/parameters used within a Function

In the case of MATCH the final parameter (match_type) is optional (denoted by [ ]) - when omitted it is default of 1, when set to 0 it conducts an exact match based on criteria value so it follows that with data sorted in ASC order:

MATCH(criteria,range,0)

returns first index position of criteria within the range whereas

MATCH(criteria,range)

returns the last instance (parameter omitted means default match_type setting of 1)

I can then use the above with INDEX in reference form to create a range:

INDEX(A:A,MATCH(criteria,range,0)):INDEX(A:A,MATCH(criteria,range))

will say generate a range of A1:A5 (1 & 5 being determined by the respective MATCH functions)

Against which I can apply my *other* function - in this case MIN

=MIN(INDEX(...):INDEX(...))

richTheGrill · #10 06-25-2009, 01:15 PM

Alrighty, thanks again, I also work with Access quite a bit, I bet you folks have a form for that also? I am sorry to say I went to school for this, but it has been a few years and I have forgotten much. In this new job I have, I will be designing and managing both data bases and spread sheets. I think with time I will start to remember. . . I Hope

Thanks for your help!!

Welcome to the Excel Forum

Forum Statistics