• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Lori

Active Member
Given a single column list containing text items with numbers following, find the item with the largest sum.

For example in the following list your formula should return c (with a sum of 9).

a
1
3
b
1
2
5
c
2
7
d
8

Your answer should be a single formula involving Excel functions only that refers to a single named range x containing the list of values.

You may assume numbers are positive. Good luck!
 

Sajan

Excel Ninja
Hi,
Good challenge!

I am sure the following array formulas can be optimized, but here is what I have so far:

=INDEX(x,MATCH(MAX(MMULT(TRANSPOSE(ROW(x)^0),IFERROR((LOOKUP(ROW(x),1/ISERR(-x)*ROW(x),x)=TRANSPOSE(x))*x,0))),MMULT(TRANSPOSE(ROW(x)^0),IFERROR((LOOKUP(ROW(x),1/ISERR(-x)*ROW(x),x)=TRANSPOSE(x))*x,0)),0))
enter with Ctrl + Shift + Enter

if only integer numbers can be assumed, then the following array formula would also work:
=INDEX(x,ROUND(MOD(MAX(MMULT(IFERROR((TRANSPOSE(LOOKUP(ROW(x),1/ISERR(-x)*ROW(x),x))=x)*TRANSPOSE(x),0),ROW(x)^0)+MATCH(ROW(x),ROW(x),0)%),1)*100,0))
enter with Ctrl + Shift + Enter

Cheers,
Sajan.
 

Lori

Active Member
Sajan - That's a great solution and you got there much quicker than me. I see ideas from the previous challenge being implemented here.

As you say, there are possibilities for further efficiency improvements. One way is to use the FREQUENCY function:
a) to pick out the largest sum (which should save repeating the formula for the array of sums)
b) to find the length of the runs of numbers to sum (which should be quicker than the matrix approach.)

Mine runs to 120 characters, but there may well be shorter versions.
 

Sajan

Excel Ninja
Hi Lori,
I look forward to seeing your FREQUENCY based solution.

Having seen a few (unique) examples of your formulas elsewhere, I am a fan!

Regards,
-Sajan.
 

Sam Mathai Chacko

Active Member
Well, I'll hope I can pay up for this hectare space some day, but I'll go with this mega formula.

=INDEX(X,INDEX(SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),MATCH(MAX(SUMIF(OFFSET(X,SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),,IFERROR(SMALL(IF(ISTEXT(OFFSET(X,1,)),ROW(OFFSET(X,1,))),ROW(INDIRECT("1:"&SUM(--ISTEXT(X)))))-1,ROWS(X))-SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),),"<>0")),SUMIF(OFFSET(X,SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),,IFERROR(SMALL(IF(ISTEXT(OFFSET(X,1,)),ROW(OFFSET(X,1,))),ROW(INDIRECT("1:"&SUM(--ISTEXT(X)))))-1,ROWS(X))-SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),),"<>0"),)))
 

Abhijeet R. Joshi

Active Member
@Sajan & Sam, Guys can please help me understand this formula?

@Sajan: I have seen you often use the MMULT Formulas and the (^) sign, what are these used for? Something specific?
 

Sam Mathai Chacko

Active Member
Abhijeet, basically, this is what my formula is doing

=INDEX(X,INDEX({1,4,8,11},MATCH(MAX(SUMIF(OFFSET(X,{1,4,8,11},,{2,3,2,1},),"<>")),SUMIF(OFFSET(X,{1,4,8,11},,{2,3,2,1},),"<>"),)))

I'm sure you can make out what's happening in there now. :)
 

Sajan

Excel Ninja
Hello Abhi,
MMULT stands for Matrix Multiplication. (As you may recall, a matrix is an array with one or more dimensions.)

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}

Cheers,
Sajan.
 

Luke M

Excel Ninja
Hello Abhi,
MMULT stands for Matrix Multiplication. (As you may recall, a matrix is an array with one or more dimensions.)

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}

Cheers,
Sajan.
Any chance you would want to write up a detailed article either for the blog, or here on the forum about this nifty little function? I tool have seen you use it often, but do not fully understand how it works or how to make it do these tricks (similar to how one may know what SUMPRODUCT literally does, but not understand that it can check criterias)
 

Lori

Active Member
Well, I'll hope I can pay up for this hectare space some day, but I'll go with this mega formula.

=INDEX(X,INDEX(SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),MATCH(MAX(SUMIF(OFFSET(X,SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),,IFERROR(SMALL(IF(ISTEXT(OFFSET(X,1,)),ROW(OFFSET(X,1,))),ROW(INDIRECT("1:"&SUM(--ISTEXT(X)))))-1,ROWS(X))-SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),),"<>0")),SUMIF(OFFSET(X,SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),,IFERROR(SMALL(IF(ISTEXT(OFFSET(X,1,)),ROW(OFFSET(X,1,))),ROW(INDIRECT("1:"&SUM(--ISTEXT(X)))))-1,ROWS(X))-SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),),"<>0"),)))
Sam - That seems to work well for the first row, but maybe a small adjustment is needed in case the list is located elsewhere. Here's some further clues relating to my suggestions above (to help save on costly real estate :)):
a) FREQUENCY(-L,-y)
b) FREQUENCY(-ROW(x),-ISTEXT(x)*ROW(x))
where L is a large number and y is a numeric array.
 

Sajan

Excel Ninja
Hi Luke,
I had drafted an article on MMULT a while back, but never published it. Perhaps I should "dust it up" and submit for consideration to Hui, Chandoo, etc. Let me know if you would like to receive a draft copy for early feedback or comments. (As an admin on the site, you can probably get my e-mail from my profile.)

Regards,
Sajan
 

Sajan

Excel Ninja
Hi Lori,
That is a cool trick with FREQUENCY

Here is a revised formula (which I will now look to optimize further):
=INDEX(x,MATCH(1,FREQUENCY(-9999, -MMULT(IFERROR((TRANSPOSE(LOOKUP(ROW(x),1/ISERR(-x)*ROW(x),x))=x)*TRANSPOSE(x),0),ROW(x)^0)),0))

enter with Ctrl + Shift + Enter

Thanks!
-Sajan.
 

Sajan

Excel Ninja
Hi,
Replacing INDEX(MATCH()) with LOOKUP shortens it a bit more, but I am sure this can be optimized further:
=LOOKUP(1,1/FREQUENCY(-9999,-MMULT(IFERROR((TRANSPOSE(LOOKUP(ROW(x),1/ISERR(-x)*ROW(x),x))=x)*TRANSPOSE(x),0),ROW(x)^0)),x)

enter with Ctrl + Shift + Enter

-Sajan.
 

Sam Mathai Chacko

Active Member
Lori, yes I agree about the row location. But thanks a lot for that innovative use of FREQUENCY. That would have never occured to me!

Sajan, here's a shorter version of your formula

=LOOKUP(1,1/FREQUENCY(-9^9,-MMULT((TRANSPOSE(LOOKUP(ROW(X),ROW(X)/ISERR(-X),X))=X)*N(TRANSPOSE(X)),ROW(X)^0)),X)
 

Sajan

Excel Ninja
Hi Sam,
Here is another revision that shortens the formula a bit more:
=LOOKUP(1,1/FREQUENCY(-9^9,MMULT(N(TRANSPOSE(LOOKUP(ROW(x),ROW(x)/ISERR(-x),x))=x),IFERROR(-x,0))),x)

entered with Ctrl + Shift + Enter

-Sajan.
 

NARAYANK991

Excel Ninja
Hi Lori ,

Can you say what should be the output in the following data ?

Code:
  p
311
  a
  p
  33
  44
155
  66
  17
  r
  d
  e
399
  s
Should it be p or e ?

Narayan
 

Sam Mathai Chacko

Active Member
Interesting find Narayan. Made me add an IFERROR() to my original mega formula (which by the way still falls under Lori's radar of row location, but is still effective) ;)

=INDEX(X,INDEX(SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),MATCH(MAX(IFERROR(SUMIF(OFFSET(X,SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),,IFERROR(SMALL(IF(ISTEXT(OFFSET(X,1,)),ROW(OFFSET(X,1,))),ROW(INDIRECT("1:"&SUM(--ISTEXT(X)))))-1,ROWS(X))-SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),),"<>0"),)),SUMIF(OFFSET(X,SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),,IFERROR(SMALL(IF(ISTEXT(OFFSET(X,1,)),ROW(OFFSET(X,1,))),ROW(INDIRECT("1:"&SUM(--ISTEXT(X)))))-1,ROWS(X))-SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),),"<>0"),)))
 

Sajan

Excel Ninja
Hi Narayan,
By tweaking the above formula slightly, we can get the string with the largest sum, instead of the cumulative sum.

=LOOKUP(1,1/FREQUENCY(-9^9,MMULT(N(TRANSPOSE(LOOKUP(ROW(x),ROW(x)/ISERR(-x)))=ROW(x)),IFERROR(-x,0))),x)

enter with Ctrl + Shift + Enter

Regards,
Sajan.
 

Lori

Active Member
Sajan (& Sam) - Nice tweaks you definitely win on formula length now! I'll post my approach in a day or two...

Narayank991 - That's a very valid point. I'd be happy to accept either approach (p or e in your example) providing it's consistent and meets the initial spec which those posted so far do.

I should have made explicit that the text values didn't repeat and also that blanks should be ignored and not treated as headers. (The question was actually based on a real world example, where data came from a long report in list format where the headers were profit centres that were known to be unique.)
 

Lori

Active Member
Abhijeet, basically, this is what my formula is doing

=INDEX(X,INDEX({1,4,8,11},MATCH(MAX(SUMIF(OFFSET(X,{1,4,8,11},,{2,3,2,1},),"<>")),SUMIF(OFFSET(X,{1,4,8,11},,{2,3,2,1},),"<>"),)))

I'm sure you can make out what's happening in there now. :)
Sam, can you refine this idea? The result may be a few characters longer than the other way but is comparatively very efficient and could easily be extended to many thousands of rows.

Hint: Try replacing the {2,3,2,1} array with my other suggestion above and {1,4,8,11} with ROW(x).
 

Sajan

Excel Ninja
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)
 
Top