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.
J-Walk:
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.
20 April 2005, 8:10 pmBob Umlas:
In B11, array-enter:
=SUM(LARGE(($A$2:$A$8=$A11)*($B$2:B$8),{1,2}))
fill right & down.
20 April 2005, 9:03 pmThanks!
Bob Umlas:
Oh — and please note the relative references and absolute ones — they’re key to the solution.
20 April 2005, 9:04 pmJuan Pablo González:
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).
20 April 2005, 10:09 pmJuan Pablo González:
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}))
20 April 2005, 10:11 pmTJM:
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
21 April 2005, 1:34 amPaul:
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.
21 April 2005, 1:57 amBob Phillips:
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
24 25 25 30 30
24 25 26 27 28
In my tests, Bob’s response gives
24 25 26 30 30
24 27 27 27 28
and Juan Pablo also gives
24 25 26 30 30
24 27 27 27 28
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.
21 April 2005, 2:20 amdoco:
My brain hurts just reading the instructions. Its back to remedial reading for me…
21 April 2005, 5:26 amBernie Deitrick:
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
21 April 2005, 8:27 amBernie Deitrick:
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
21 April 2005, 8:31 amBernie Deitrick:
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
21 April 2005, 8:57 amBernie Deitrick:
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
21 April 2005, 8:59 amDick Kusleika:
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.
21 April 2005, 1:52 pmBernie:
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
21 April 2005, 2:46 pmRob van Gelder:
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 “>= > =”
21 April 2005, 4:54 pmPrasanna:
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
21 April 2005, 9:10 pmDick Kusleika:
the code tag may work too, let’s try:
21 April 2005, 9:18 pmif i >= 1 adn i
Dick Kusleika:
Ooh, it even puts in monospace - mispellings and all. That’s using:
<code>Stuff goes here</code>
21 April 2005, 9:19 pmJohn Mansfield:
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.
22 April 2005, 1:13 amdoco:
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?
22 April 2005, 4:36 amJon Peltier:
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
22 April 2005, 5:03 amBernie Deitrick:
22 April 2005, 6:23 amBlogging/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.Dave W.:
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?
1 July 2005, 1:52 pm