Formula Challenge 022 - Find the item with the largest sum in a list

[Abhijeet R. Joshi]

The ^ is the exponentiation operator. In the above formula, a value is being raised to the power of zero, which you may recall is 1. So it is just a technique to build an array of 1s like {1;1;1;1;1;1;1}

Hi Sajan,
Might be I just overlooked what you had mentioned above for (^) use...
Can you please share a link pertaining to this? Being honest, I am not sure if I followed you there...

 

[Debraj]

Hi Sajan
please allow me..

Hi Abhi..

For MMULT's detail explanation.. check here..
http://www.mathwarehouse.com/algebra/matrix/multiply-matrix.php

after reading above post..

So to multiply two Array, we have only one RULE.. unlike SumProduct
* Number or COLUMN in 1st array, and number of ROW in 2nd array need to be same..
* and you cant multiply more than 2 array..

So we can create the 1st array's Number of Columns.. by any formula.. but to use MMULT, 2nd Array should have same number of Rows

Sajan mostly use TRANSPOSE to create next Array, with same number of Rows .. according to number of column in 1st array..

so to create an array {1,1,1,1,1} 5 item in a VERTICAL Array (; Represent HORIZONTAL & , represent VERTICAL Delimeter)

you can use Transpose(Row(A1:A5)) which is basically {1,2,3,4,5}

but you need all as 1.. like {1,1,1,1,1}

* Now another math.. Anything to the power 0 is 1, .. i.e 99^0 = 1
* In math's language.. {1,2,3,4,5} ToThePower 0 is equivalent to {1,1,1,1,1}
and in Excel Language.. {1,2,3,4,5} ^ 0


* Sajan mostly use =TRANSPOSE(ROW(A1:A5)^0) to create the second array of MMULT..

 

[Sajan]

Thanks Debraj!

Not sure if it is a regional setting, but on my version of Excel, a row-wise array is delimited by ";" (semi-colon) and a column-wise array is delimited by "," (comma)

Narayan has agreed to write up an explanation for these formulas. So watch this space for that!

-Sajan.

 

[Debraj]

oops.. its my fault.. at the time of copying after pressing f9, transpose was not there..

Not sure if it is a regional setting

But I am sure.. for Array.. delimiter are always..

row-wise array is delimited by ";" (semi-colon) and a column-wise array is delimited by "," (comma)

and was fixed for all regional setting..
Thanks for the catch.. and has been fixed..

 

[Abhijeet R. Joshi]

@Deb and Sajan:

I wont say that I completely followed you guys.....might be because I have a poor maths...

But I am sure, in near future I wll understand this in a better way..all thanks to your numerous formula knowledge...

 

[Lori]

Ripe for optimization, but here is the approach that Lori is outlining:

=LOOKUP(1,1/FREQUENCY(-9^9,-IFERROR(SUMIF(OFFSET(x,ROW(x)-ROW(INDEX(x,1)),,FREQUENCY(-ROW(x),-ISTEXT(x)*ROW(x)),1),"<>"),0)),x)

Sajan - quick off the mark as usual, I think that's pretty optimal. My original formula (120 chars) is very close to this:

=LOOKUP(1,1/FREQUENCY(0,IFERROR(1/SUBTOTAL(9,OFFSET(x,ROW(x)-MIN(ROW(x)),,FREQUENCY(-ROW(x),-ISTEXT(x)*ROW(x)))),-1)),x)

Thanks to all for your contributions. Looking forward to another challenge in the near future... Lori

PS. bonus points for a formula to find the sum of the maximum values (3+5+7+8)
[Sam / Sajan - am sure is easy for you guys so please allow others a go ]

 

[Sajan]

Hi Lori,
Thanks for this challenge and the clever pointers. This was a fun one. Looking forward to reading more posts from you in the future.
(I have to admit that while I can see myself eventually stumbling onto some of the solutions you have posted elsewhere, many of them seem to be in the "never in a million years" category!! I am now Googling your name to find more of those gems! Perhaps you should consider a compendium of your posts!)

Regarding your bonus points challenge, since you provided a lot of clues for its solution as well, I am sure someone will answer it quickly.

-Sajan.

 

[shrivallabha]

I think there are three people who give me formula nightmares, Sajan, Haseeb and now Lori Many combinations are out there in the previous posts.

=MAX(MMULT((TRANSPOSE(LOOKUP(ROW(x),ROW(x)/ISERR(-x),x))=x)+0,IFERROR(x/1,0)))

=MAX(IFERROR(SUMIF(OFFSET(x,ROW(x)-ROW(INDEX(x,1)),,FREQUENCY(-ROW(x),-ISTEXT(x)*ROW(x)),1),"<>"),0))

Edit: My nightmares continue...I completely misunderstood challenge. Thanks to Narayan who pointed this out to me.

Edit2: Now that I still have a chance to edit and make amends [and again it was a Narayan tip]:
=SUM(IFERROR(SUBTOTAL(4,OFFSET(x,ROW(x)-MIN(ROW(x)),,FREQUENCY(-ROW(x),-ISTEXT(x)*ROW(x)))),0))

 

[Lori]

Shriva (& Narayan), good job on the bonus question, i knew it wouldn't take long to get there. This was really a passing remark and should probably have been phrased more clearly.

Sajan, i'm flattered but i think the posts you refer to are more scientific in nature like those on the NewtonExcelBach site and not necessarily relevant to most of the visitors here. Challenges like this help hone skills, i'm not sure i'd recommend these be used in practice though. Simplicity and elegance should be the ultimate goals.

It's an interesting exercise to try to convert the formulas here into a VBA function. I have recently posted a few examples of how the array formula approach can be used in VBA, (eg: http://stackoverflow.com/questions/...-a-constant-without-looping/18515512#18515512). However, I think a solution involving collections or dictionaries would likely be the best way to go in this case.

 

[Sam Mathai Chacko]

I echo Sajan's views. Thanks once again Lori.

 

[shrivallabha]

I was following this challenge from the beginning and heading into Sajan's first post. I normally build formulas using couple of steps and see if I can combine them to give one formula.

Here's the workbook of what I had thought as possible approaches. I left them there once the solutions were posted.

And I am looking forward to Narayan's write up on this challenge. It was great fun and looking forward to some more. I don't use much Excel in the office but challenges such as these act as a good catalyst and keep your brain in good shape.

 

[Lori]

To round things off, here's a VBA user defined function adapted from Sajan's initial suggestion:

Function MaxEntry(List As Range)
    Dim i, l, d, m, x
    With Application
        i = .Evaluate("row(" & List.Address(, , , 1) & ")")
        l = .Lookup(i, .Choose(.IsText(List), i))
        d = .Delta(.Transpose(l), i)
        m = .MMult(d, .IfError(.Power(List, 1), 0))
        x = .Index(List, .Match(.Max(m), m, 0))
    End With
    MaxEntry = x
End Function

I will leave it to others to come up with a simple VBA loop function for this.

 

[shrivallabha]

Here's VBA Loop based approach. If 2nd argument is empty or "sum" it will return Max Sum and for something else like =MaxInList(x,"letter") it will return letter.

Public Function MaxInList(rng As Range, Optional strReturn As String) As Variant
Dim varText As Variant, varInput As Variant
Dim lngData() As Long
Dim intCnt As Integer
With Application
    varInput = rng.Value
    varText = Filter(.Transpose(.Evaluate("=IF(ISTEXT(" & rng.Address & ")," & rng.Address & ",""~"")")), "~", False)
    ReDim lngData(UBound(varText))
    For i = LBound(varInput) To UBound(varInput)
        If .IsText(varInput(i, 1)) Then
            intCnt = .Match(varInput(i, 1), varText, 0) - 1
        Else
            lngData(intCnt) = lngData(intCnt) + varInput(i, 1)
        End If
    Next i
    If strReturn = vbNullString Or LCase(strReturn) = "sum" Then
        MaxInList = .Max(lngData)
    Else
        MaxInList = .Index(varText, .Match(.Max(lngData), lngData, 0))
    End If
End With
End Function

 

[DBExcel]

A Power Query based example - unorthodox for the board, but still "In Excel" and easier to follow (with aid of Formula Library) using the step-wise preview window:

let
Source = Excel.Workbook(File.Contents("c:\DataHere")){[Name="Sheet1"]}[Data],
a = Table.AddColumn(Source, "abc", each try (if 1 / [Column1] <> #nan then null else null) otherwise [Column1]),
b = Table.FillDown(a,{"abc"}),
c = Table.TransformColumnTypes(b,{{"Column1", type number}}),
d = Table.RemoveRowsWithErrors(c, {"Column1"}),
e = Table.Group(d, {"abc"}, {{"Sum", each List.Sum([Column1]), type number}}),
f = Table.Sort(e,{{"Sum", Order.Descending}}),
g = Table.First(f)
in
g // for example data this returned table record {C, 9}

(notice how this kicked the but of the VBA solutions in terms of navigability and understandability)

 

[Peter Bartholomew]

This is a very old thread but I wonder how much of the archived material will need to be revisited once modern dynamic arrays are fully released? I have just used the problem to test my limitations with the new functionality. The attached file was developed with Office 365 insider and uses SEQUENCE and FILTER.

Will FILTER become the VLOOKUP of future generations?