• 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

L

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!
 
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.
 
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.
 
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.
 
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"),)))
 
@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?
 
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. :)
 
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.
 
Sajan said:
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.
Click to expand...

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)
 
Sam Mathai Chacko said:
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"),)))
Click to expand...

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.
 
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
 
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.
 
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.
 
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)
 
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.
 
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
 
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"),)))
 
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.
 
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.)
 
Sam Mathai Chacko said:
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. :)
Click to expand...

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).
 
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)
 
You must log in or register to reply here.
Back
Top