min from multiple columns if one criteria per row is met

[indesignkat]

Hello glorious Chandoovians,

I'll set this up with a very simple example: In column A we have an id code. In columns B:K we've got numeric values. I want to find the minimum of those values. That's easy enough.

Now let's complicate it by saying that each row has an id code and values across B:K, but the id code in A is not necessarily unique. There may be 1 or 3 or 15 rows with the same id code. I still want to find the minimum value for that id, regardless of what row it's on.

It seems like the aggregate function might get there, but I'm not sure how.

thoughts?

 

[indesignkat]

I forgot to mention that there can be empty cells in B:K, if that matters. I don't want it to return a zero because it matched an empty cell.

 

[Hui]

L2: =MIN(IF($B$2:$K$16>0,IFERROR(EXP(LN(($B$2:$K$16)*($A$2:$A$16=A2))),999999),)) Ctrl+Shift+Enter
Copy down

or
L2: =SUMPRODUCT(MIN(IF($B$2:$K$16>0,IFERROR(EXP(LN(($B$2:$K$16)*($A$2:$A$16=A2))),999999),))) Ctrl+Shift+Enter
Copy down

 

[NARAYANK991]

Hi ,

Try this :

=MIN(IF(($A$2:$A$16=$A2),IF($B$2:$K$16<>"",$B$2:$K$16)))

Enter this as an array formula , using CTRL SHIFT ENTER.

Narayan

 

[indesignkat]

Amazing, as per usual. You guys are the best.

 

[Khalid NGO]

Hi,

Or you can use same logic in AGGREGATE function with just Enter:

=AGGREGATE(15,6,1/(($A$2:$A$16=$A2)/($B$2:$K$16<>""))*$B$2:$K$16,1)

Regards,

 

[Peter Bartholomew]

This only differs from #4 in terms of presentation
=IF(first.occurence?, MIN(ID.values), "" )

It is defined to return the minimum value associated with an ID only for the first occurrence of the ID, blank otherwise. The relevant ID.values are determined by
= IF((Table1[id]=Table1[@id]), IF((values<>""),values) )
where 'values' covers the part of the table
=Table1[[value 1]:[value 10]]
The Boolean test for the first occurrence of an ID is
= MATCH(Table1[@id],Table1[id],0)=k
where 'k', the record index, is given by
= ROW(Table1[@]) - ROW(Table1[#Headers])

Since the array calculation is performed by default for named formulas, CSE is not needed.

Confession: I have simply used this example as a test of whether the way I wish to program Excel can always be made to work.

 

[Hui]

Peter

There are many advantageous of using named formula in calculations and the two main reasons are Simplification/Clarity, Efficiency & Speed

Just be aware that if you are the only user of a file, great,
But most user get lost when it comes to using and understanding named formula

 

[Peter Bartholomew]

@Hui
The points you make are true and are of concern to me. The reality is that my solutions are actually more mundane than the creative single-cell formulas that others here have mastered.

My problem is, that once I have incurred the setup effort that allows me to develop a solution though a sequence of reasonably logical steps, I am reluctant to unpick it. To me, that is a task for Excel and not a human being. By and large Excel does not make mistakes when replacing names by direct references and, ultimately, memory addresses or breaking array formulas down to components for calculation; I do.

My problem in this context is, if the questioner looks at the solution in blank amazement, little is achieved.

 

[NARAYANK991]

Hi Peter ,

I agree with you.

Most people who approach this and other forums insist on complex and complicated formulae which do not use helper cells or columns.

I am amazed by this approach to Excel as a productive tool.

There are hundreds of worksheets , thousands of columns , and a million rows that Excel offers , and yet users want to use complex formulae so that they can save probably a few dozen columns / a few hundred rows.

I invariably use helper cells / columns / rows , in part because I cannot think of the complex formulae which do not need them , but also because I wish to show the user who had the problem , that with the basic knowledge that they had , they could have arrived at the solution if only they had thought the problem through.

There have also been cases where users ask for complex formulae , and then complain that their workbook has become sluggish in its response , and hence look for alternatives !

The ideal would be if we could formula-based patterns for all the basic tasks such as sorting , extracting unique items , eliminating blanks ,...

Narayan

 

[Peter Bartholomew]

The ideal would be if we could [develop] formula-based patterns for all the basic tasks such as sorting , extracting unique items , eliminating blanks ,...

NARAYAN
Something I attempted a few years ago was to develop an algorithm (in this instance the merging of two sorted lists) as a module by placing in its own single sheet workbook with locally-scoped names only. The idea was to move the module to a main workbook (causing the module's workbook to close) and then use links between the local names and workbook names to fulfil the function of a parameter list and to return values from the module.

I think your idea would be most valuable if it could be backed up by a library of standard methods and perhaps the attached might offer first faltering steps in that direction.