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. In B11, array-enter:
   =SUM(LARGE(($A$2:$A$8=$A11)*($B$2:B$8),{1,2}))

   fill right & down.
   Thanks!

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

4. 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. 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. 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. 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. My brain hurts just reading the instructions. Its back to remedial reading for me…

10. 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. 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. 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. 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. 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. 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. 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.  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. 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.