Max of two columns, of 7 columns possible.

[bines]

Hello everyone,

I am looking for a formula shortened, to give directly to the solution as the cell, H2, without helper line, as Line 3.

Also, get the location of the two columns of Max.

I would like a solution without volatile function.

EXCEL 2010.


Thank you !

[zbor]

Here.
Confirm with ctrl+shift+enter (not just enter)

Please Login or Register  to view this content.

[bines]

Hi zbor,

If possible without volatile functions ?

Thank you !

[zbor]

I didn't find a way except with helper column.

[bines]

I think that the function starts = MAX(COUNTIFS(INDEX($A$5:$A$105,,{1;2;3;4;5;6}),$J$5

[zbor]

I've tried that but without sucess. It doesn't evaluate range.

[bines]

This link ,http://excelxor.com/2014/09/05/index...ray-of-values/ ,There is an explanation, but could not implement it, in my case ?

[FDibbins]

Hi zbor,

If possible without volatile functions ?

Thank you !

Why do you have a convern/reluctance to use an array formula?

[bines]

Hi FDibbins,

I have no desire to array formula,I have a very heavy workbook, doing all the time calculations.

[daddylonglegs]

Are you trying to avoid "array formulas" (i.e. formulas that need to be confirmed with CTRL+SHIFT+ENTER) or volatile functions (or both)?

These two approaches avoid volatile functions but need to be "array entered"

=MAX(FREQUENCY(IF(A5:A105=J5,IF(B5:G105=K5,COLUMN(B5:G105))),COLUMN(B5:G105)))

or

=MAX(MMULT(TRANSPOSE(ROW(A5:A105)^0),(A5:A105=J5)*(B5:G105=K5)))

It might be possible to get a version that doesn't require array entry but it might not be any faster

[bines]

Hi daddylonglegs,

Is it possible to convert the first formula, the formula with AGGREGATE function ,So that's great !

Thank you !

[XOR LX]

Hi.

I am not clear on why you are so keen on avoiding an array-formula solution. Can you clarify your reasons?

Regards

[bines]

Hi XOR LX,


I get current data, the stock market, all the time, carried out complex calculations, as well as a very large file, very important to me calculation speed.

[XOR LX]

But here there really is no "natural" non-CSE solution, and so any such construction is likely to be sufficiently convoluted as to be, if anything, more resource-heavy than the equivalent CSE set-up.

For example, daddylongleg's second offering is, in my opinion, probably the most efficient (though I have not tested this). If you really wanted to, you could make it non-CSE, at the cost of several additional function calls, following my advice here:

http://excelxor.com/2014/10/07/non-array-transpose/

i.e.:

=MAX(MMULT(TRANSPOSE(N(INDEX(ROW(A5:A105),,))^0),(A5:A105=J5)*(B5:G105=K5)))

though I assure you that this version will most certainly not be more efficient: in fact, far less, undoubtedly.

Forgive me, but I hope that you are not one of the not-inconsiderable number of people out there who somehow believe that array formulas are to be avoided at all costs. What most of these people fail to realise is that functions which they consider to be non-array, such as LOOKUP, SUMPRODUCT, etc., are in fact precisely that; they simply do not require CSE in order to coerce that functionality.

Even more worrying, in my opinion, are the increasing number of people who are constantly employing constructions using the insertion of one or more additional INDEX functions, solely with the purpose of avoiding CSE, though not realising that the resulting constructions are not only still array formulas (albeit not requiring a certain keystroke combination), but, more tellingly, they are actually more resource-heavy than the equivalent CSE construction.

Regards

[bines]

Hi XOR LX,

I want to understand,This formula =MAX(COUNTIFS($A$5:$A$105,$J$5,OFFSET($A$5:$A$105,,{1,2,3,4,5,6}),$K$5)) with OFFSET and COUNTIFS ,Such a structure can not be constructed with INDEX and COUNTIFS ,Without volatile and Without CSE ?

Thanks for your help !

David

[XOR LX]

I want to understand,This formula =MAX(COUNTIFS($A$5:$A$105,$J$5,OFFSET($A$5:$A$105,,{1,2,3,4,5,6}),$K$5)) with OFFSET and COUNTIFS ,Such a structure can not be constructed with INDEX and COUNTIFS ,Without volatile and Without CSE ?

Unfortunately not, no.

It's clear that you've read my article re generating arrays with INDEX, and are hoping for some solution along those lines, but unfortunately it does not appear that this technique can be extended to such cases, and this is one situation where a function such as OFFSET has the upper hand.

In essence, the issue is that there is apparently no way to amalgamate the two otherwise-useful properties of INDEX: firstly, that the omission of a column (or row) parameter will coerce the entire row (or column) to be returned, as described here:

http://excelxor.com/2014/08/18/index...e-rowscolumns/

and secondly, that we can force, using the technique I describe here:

http://excelxor.com/2014/09/05/index...ray-of-values/

an array of returns from INDEX.

Ideally, you would be hoping for something like:

=MAX(COUNTIFS($A$5:$A$105,$J$5,INDEX($B$5:$G$105,,N(IF(1,{1,2,3,4,5,6}))),$K$5))

maybe expecting this to resolve to the equivalent of:

=MAX(COUNTIFS($A$5:$A$105,$J$5,{$B$5:$B$105,$C$5:$C$105,$D$5:$D$105,$E$5:$E$105,$F$5:$F$105,$G$5:$G$105},$K$5))

but unfortunately it doesn't.

And attempting to use INDEX's fourth parameter, area_num, fares no better, since:

=MAX(COUNTIFS($A$5:$A$105,$J$5,INDEX(($B$5:$B$105,$C$5:$C$105,$D$5:$D$105,$E$5:$E$105,$F$5:$F$105,$G$5:$G$105),,,N(IF(1,{1,2,3,4,5,6}))),$K$5))

equally goes nowhere.

Deriving such a functionality really would be a "holy grail", though it simply does not appear possible.

Besides, did you even read my article re the dangers of trying to "avoid" array formulas?

Regards

[bines]

Hi XOR LX,

I really like your site !
I realized that everything you do with OFFSET , you can do exactly the same with INDEX !
I understand that the function TRANSPOSE ,built-in Excel is array and it must have {},But it is a "normal" function.
Then the second option gave me daddylongleg, is better !

Now I need to find, the two columns of reference ,In my example, the extended function

H2=MAX(A3:F3)
I2=AGGREGATE(14,6,COLUMN(A3:F3)/(A3:F3=H2),1)
J2=AGGREGATE(14,6,(ROW(A3:F3)-ROW(A3)+1)/(A3:F3=H2),1)+I2
How do I do this short function H2=MAX(MMULT(TRANSPOSE(ROW(A5:A105)^0),(A5:A105=J5)*(B5:G105=K5))), and no line 3

Thanks for your help !

David

[bines]

Reference of two columns

I2=AGGREGATE(14,6,COLUMN(A1:G1)/(MMULT(TRANSPOSE(ROW(A5:A105)^0),(A5:A105=J5)*(B5:G105=K5))=H2),1),WITH CSE (COLUMN 2)

J2==AGGREGATE(14,6,COLUMN(B5:G105)/(MMULT(TRANSPOSE(ROW(A5:A105)^0),(A5:A105=J5)*(B5:G105=K5))=H2),1),,WITH CSE (COLUMN 3)

H2=MAX(MMULT(TRANSPOSE(ROW(A5:A105)^0),(A5:A105=J5)*(B5:G105=K5))),WITH CSE

[bines]

Hi daddylongleg ,XOR LX ,

There is a question more difficult than the previous one,I hope, that it's okay with you, and not excessive .
Same range, the same data, 7 columns, but there are 19 combinations, it seems that here have the function TRANSPOSE .
With the function OFFSET , it works like this,
=MAX(COUNTIFS(OFFSET($A$9:$A$109,,{0,1,2,3,4,5}),$J$5,OFFSET($A$9:$A$109,,{1;2;3;4;5;6}+{0,1,2,3,4,5}),$K$5))

Thanks for your help !

David

[bines]

Can shorten this formula ?

=MAX(MMULT(TRANSPOSE(ROW(A9:A109)^0),(A9:A109=J5)*(B9:G109=K5)),MMULT(TRANSPOSE(ROW(A9:A109)^0),(B9:B109=J5)*(C9:G109=K5)),MMULT(TRANSPOSE(ROW(A9:A109)^0),(C9:C109=J5)*(D9:G109=K5)),MMULT(TRANSPOSE(ROW(A9:A109)^0),(D9:D109=J5)*(E9:G109=K5)),MMULT(TRANSPOSE(ROW(A9:A109)^0),(E9:E109=J5)*(F9:G109=K5)),MMULT(TRANSPOSE(ROW(A9:A109)^0),(F9:F109=J5)*(G9:G109=K5)))

The same solution, as =MAX(COUNTIFS(OFFSET($A$9:$A$109,,{0,1,2,3,4,5}),$J$5,OFFSET($A$9:$A$109,,{1;2;3;4;5;6}+{0,1,2,3,4,5}),$K$5))

[XOR LX]

Hi.

Thanks for your questions. They sound interesting. I haven't got time to look at them now, but I will tomorrow morning.

Cheers!

[XOR LX]

I do not know of any way in which the construction involving OFFSET can be abbreviated here (either using MMULT or any other functions).

To be honest, I very much doubt that it can be improved: as I said, despite its volatility, OFFSET has properties that are unique to that function.

Regards

[bines]

Hi XOR LX ,

I try to do with the function CHOOSE ,Something is missing, or not possible at all ?

=MAX(MMULT(TRANSPOSE(ROW(A9:A109)^0),CHOOSE({1,2,3,4,5,6},(A9:A109=J5)*(B9:G109=K5),(B9:B109=J5)*(C9:G109=K5),(C9:C109=J5)*(D9:G109=K5),(D9:D109=J5)*(E9:G109=K5),(E9:E109=J5)*(F9:G109=K5),(F9:F109=J5)*(G9:G109=K5))))

Thanks for your help !

[XOR LX]

Hi.

You have two issues here. The first is that the ranges you are passing to CHOOSE are not of an equal dimension.

The product:

(A9:A109=J5)*(B9:G109=K5)

results in a 101-row-by-6-column matrix.

And the product:

(A9:A109=J5)*(B9:G109=K5)

results in a 101-row-by-5-column matrix

etc., etc. until your final product:

(F9:F109=J5)*(G9:G109=K5)

which is a 101-row-by-1-column matrix.

CHOOSE requires that the ranges passed be of an equal dimension. When this is not the case, it resolves this conflict by artificially expanding each of the ranges passed so that they are of an equal dimension to that of the largest such range.

But more importantly, the second issue is that, even if your ranges were of equal dimensions, you should know that CHOOSE does not operate in the way you imagine: arrays of more than one dimension are not each passed on in their entirety to the function operating on the results of that CHOOSE function.

For example, for the following data in A1:F3:

	A	B	C	D	E	F
1	1	2	3	4	5	6
2	7	8	9	10	11	12
3	13	14	15	16	17	18

the following formula:

=SUM(CHOOSE({1;2;3},A1:B3,C1:D3,E1:F3))

is not equivalent to:

=SUM(A1:B3,C1:D3,E1:F3)

but rather to:

=SUM(A1:B1,C2:D2,E3:F3)

i.e. the sum of the first row of the first range, the second row of the second range and the third row of the third range.

and:

=SUM(CHOOSE({1,2,3},A1:B3,C1:D3,E1:F3))

returns #N/A, since it is equivalent to summing the first column from A1:B3 (i.e. A1:A3 - {1;7;13}), the second column from C1:D3 (i.e. D1:D3 - {4;10;16}) and the third column from E1:F3 (which does not exist!!).

And that's just with the three ranges being passed being of an equal dimension. I leave it to you to investigate various results when one or more of the dimensions are unequal.

Regards

[bines]

Hello everyone ,

Need a little help,I want another version, this formula given by daddylonglegs

=MAX(FREQUENCY(IF(A5:A105=J5,IF(B5:G105=K5,COLUMN(B5:G105))),COLUMN(B5:G105)))

I got this formula, without the need CSE ,

=FREQUENCY(($A$5:$A$105=$J$5)*($B$5:$G$105=$K$5)*COLUMN($B$5:$G$105),COLUMN($B$5:$G$105)-1)

{526;18;20;14;10;9;9},526 is redundant ,Otherwise, all entries are correct.

Thanks for your help !