# Formula Challenge

Hector needs a formula that has me stumped. Of course, there are constraints: You can’t use a UDF and you can’t use any helper columns.

He has a range of values like this:

 A B C D E F 1 P1 P2 P3 P4 P5 2 T1 13 7 13 1 14 3 T2 10 11 6 13 14 4 T1 9 12 2 15 14 5 T2 14 13 11 13 2 6 T1 11 1 4 15 14 7 T2 8 9 12 1 10 8 T1 6 3 12 10 6 9 … 10 P1 P2 P3 P4 P5 11 T1 12 T2

Here’s the right answer

 A B C D E F 10 P1 P2 P3 P4 P5 11 T1 24 25 25 30 30 12 T2 24 25 26 27 28

The formula is the sum of the two largest values from a range. The range is all the values in the column whose row header is the same as the row header by the formula (col A) AND all columns to the left. Ex: C11 is the sum of B2 and C4 – the two highest values in cols B & C with T1 in the row.

My brain hurts from thinking about this. I convinced myself it could be done, but now I don’t think so.

P.S. Thanks to John and Rob for the css tables.

Posted in Uncategorized

## 24 thoughts on “Formula Challenge”

1. I played around with this for 15 minutes, but then I had to leave the room. The charred brain cells were getting to be a fire hazard.

It would definitely be do-able without the stipulation of no helper columns. And it would be piece o’ cake with a UDF.

2. Bob Umlas says:

In B11, array-enter:
=SUM(LARGE((\$A\$2:\$A\$8=\$A11)*(\$B\$2:B\$8),{1,2}))

fill right & down.
Thanks!

3. Bob Umlas says:

Oh — and please note the relative references and absolute ones — they’re key to the solution.

4. Juan Pablo González says:

Bob does the job, but I don’t know if it assumes that P1,P2,P3,etc. are always in the same order.

=SUMPRODUCT(LARGE((Sheet1!\$A\$2:\$A\$8=\$A11)*OFFSET(RngCol,0,1,,MATCH(Sheet1!B\$10,Sheet1!\$1:\$1,0)-1),{1,2}))

seems to do the job (no need to array enter).

5. Juan Pablo González says:

Oops, forgot to remove the named range…

=SUMPRODUCT(LARGE((\$A\$2:\$A\$8=\$A11)*OFFSET(\$A\$2:\$A\$8,0,1,,MATCH(B\$10,\$1:\$1,0)-1),{1,2}))

6. TJM says:

Ha Ha – putting the wrong answers in P2 / P3 columns slowed me down a bit working out how it was supposed to work. (A bit ? – now I’ve got a headache too!)

Do you have to be “special” to think in arrays :-)

7. Excuse me for being contentious and I’m certain Bob Umlas has the right formula but it doesn’t produce the answer that is stated as the right answer. Are you sure the answer is the correct answer to the problem stated.

8. Bob Phillips says:

I am not sure if people are understanding the reuirement as I am. Hector wants the sum of the 2 largest values in an array. This array will be the largest value in each row for the columns being considered. So, in looking at a total for column B, the loargest value in row 2 is 13. For column C, the largest value in row 2 is still 13, and so on until column F where it becomes 14. So in summing the 2 largest values, these are the values to be considered for row 2. Hector’s want’s results of

2425253030
2425262728

In my tests, Bob’s response gives

2425263030
2427272728

and Juan Pablo also gives

2425263030
2427272728

both close, but not quite there.

Conclusion – you need a temporary arary of the largest row values to date (to date being relative to the column being considered), and then summing the 2 largest of these. So like Dick, I am thinking bit can’t be done as specced.

9. doco says:

My brain hurts just reading the instructions. Its back to remedial reading for me…

10. Bernie Deitrick says:

This is actually two formulas added together: an array formula to return the MAX, and another that returns the MAX where the row is not the same as the MAX value’s row.

=MAX((\$A\$2:\$A\$8=\$A11)*(\$B\$2:B\$8))+MAX((\$A\$2:\$A\$8=\$A11)*(\$B\$2:B\$8)*((ROW(\$B\$2:B\$8))(MAX(IF((\$A\$2:\$A\$8=\$A11)*(\$B\$2:B\$8)=MAX((\$A\$2:\$A\$8=\$A11)*(\$B\$2:B\$8)),ROW(\$B\$2:B\$8))))))

Array entered. Now I need to get my brain to stop hurting ;-)

Bernie

11. Bernie Deitrick says:

Oh, I forgot to say that the formula should be array-entered in B11. And I’m pretty sure that I have too many parens, but it worked, so I stopped. Someone will post his minimalist response to my too-large formula sometime soon, I’m sure.

Bernie

12. Bernie Deitrick says:

For some reason, the my post had a converted to a *

The correct formula should be

=MAX((\$A\$2:\$A\$8=\$A11)*(\$B\$2:B\$8))+MAX((\$A\$2:\$A\$8=\$A11)*(\$B\$2:B\$8)*((ROW(\$B\$2:B\$8))(MAX(IF((\$A\$2:\$A\$8=\$A11)*(\$B\$2:B\$8)=MAX((\$A\$2:\$A\$8=\$A11)*(\$B\$2:B\$8)),ROW(\$B\$2:B\$8))))))

Just in case there was something automated behind that conversion (like the winky-smiley face icon)
=MAX((\$A\$2:\$A\$8=\$A11)*(\$B\$2:B\$8))+MAX((\$A\$2:\$A\$8=\$A11)*(\$B\$2:B\$8)*((ROW(\$B\$2:B\$8))NOT-EQUAL-HERE(MAX(IF((\$A\$2:\$A\$8=\$A11)*(\$B\$2:B\$8)=MAX((\$A\$2:\$A\$8=\$A11)*(\$B\$2:B\$8)),ROW(\$B\$2:B\$8))))))

Bernie

13. Bernie Deitrick says:

Dick,

There is something that changed the Not Equal “” in both of my replies with formulas – the first time to a *, and the second time to nothing.

Bernie

14. Bernie: It thinks their html tags. You have to use the escape code < and > which I typed as ampersand ell tee semicolon and ampersand gee tee semicolon.

I really need to make that easier.

15. Bernie says:

Thanks, Dick, for explaining that.

Here’s a test of the escape code version:

=MAX((\$A\$2:\$A\$8=\$A11)*(\$B\$2:B\$8))+MAX((\$A\$2:\$A\$8=\$A11)*(\$B\$2:B\$8)*((ROW(\$B\$2:B\$8))<>(MAX(IF((\$A\$2:\$A\$8=\$A11)*(\$B\$2:B\$8)=MAX((\$A\$2:\$A\$8=\$A11)*(\$B\$2:B\$8)),ROW(\$B\$2:B\$8))))))

Bernie

16. I’ve seen the pre tag used on other tech blogs.

I’m not sure if it works here… let’s try:

if i >= 1 and i > 1 or i 15 or “i” & 1 & 5 = 1 then msgbox “>= > =”

17. Prasanna says:

The following array formula also gives the answer.

Formula in B11

{=LARGE(IF((\$A\$2:\$A\$9=\$A11)*(\$B\$1:\$H\$1=B\$10),\$B\$2:\$H\$9),1)+LARGE(IF((\$A\$2:\$A\$9=\$A11)*(\$B\$1:\$H\$1=B\$10),\$B\$2:\$H\$9),2)}

Prasanna

18. the code tag may work too, let’s try:

if i &gt;= 1 adn i
19. Ooh, it even puts in monospace – mispellings and all. That’s using:

<code>Stuff goes here</code>

20. Dick – Along the lines of Rob’s comment – I’ve had pretty good luck with the pre tag i.e.

Code or Formulas here

To control the look of the contents within the pre tags, I’ve set my style sheet up like this:

/*Code*/
pre {
font-size: 12px;
font-family: verdana, times new roman, times, serif;
font-weight: normal;
color: #000;
border: 1px solid #000;
margin: 6px 60px;
padding: 5px 8px;
background: #FFFFCC;
}

Then, I have to define greater than, less than, and ampersand symbols to their ASCII equivilents within the pre tags. At that point the code or formulas will validate to XHTML (hopefully).

I could build a space shuttle in my back yard faster than it took me to figure out how all of this worked.

21. doco says:

Honestly, I have never been able to figure out the desire to build mega formulas (not that y’all do)? Learning VB/VBA (or swahilie) is much the simpler than either building space shuttles or mega formulas; and much easier to understand more than 24 hours (or 24 minutes) after creation. ;-)

The formula listed above is a real jaw dropper and I still have no idea what it’s doing? Is there a trick to disecting those things? :eek:

22. John –

“font-family: verdana, times new roman, times, serif;”

Isn’t it conventional to use a nonproportional font, like Courier, for the pre tag content?

– Jon

23. Bernie Deitrick says:
Blogging/comment adding software should be smart enough to treat everything the great unwashed public posts as if it were code, so that our  ‘s, and geeks-know-what-else don’t disappear.
24. Dave W. says:

Do the row headers have to alternate? Can’t you sort the entire list by column A and then your array defintions are much easier. Or does that violate the “no helper columns” rule?

Posting code? Use <pre> tags for VBA and <code> tags for inline.