0

Hi,

I have 7 soccer matches results: 

             B   D           

             Team A       
2001        0   - 12      
2002        1   - 5        
2003        1   - 11        
2004        4   - 1         
2005        1   - 6  
2006        0   - 5
2007        1   - 2

and this formula that gives me the best soccer score out of them

=INDEX(B2:B8,SUMPRODUCT((B2:B8-D2:D8*1.01=MAX(B2:B8-D2:D8*1.01))*ROW(B2:B8))-ROW(B1))&" - "&INDEX(D2:D8,SUMPRODUCT((B2:B8-D2:D8*1.01=MAX(B2:B8-D2:D8*1.01))*ROW(B2:B8))-ROW(B1))

This works perfectly so far since the resulted value is 1-2. However, since Im going to be adding scores below B8 I need to modify this formula so it will always reference the last cell with data. So I tried this:

=INDEX(B:B,SUMPRODUCT((B:B-D:D*1.01=MAX(B:B-D:D*1.01))*ROW(B:B))-ROW(B1))&" - "&INDEX(D:D,SUMPRODUCT((B:B-D:D*1.01=MAX(B:B-D:D*1.01))*ROW(B:B))-ROW(B1))

but it didnt work. How can I fix this?

Thank you.

Cain Nuke
  • 179
  • 1
  • 2
  • 10
  • This answer might be helpful http://superuser.com/a/982611/457808. – Kyle Nov 03 '15 at 15:17
  • Thank you for the link but I am afraid I didnt get it at all. Its all greek to me. As far as I could understand, its near impossible what I am trying to achieve? – Cain Nuke Nov 03 '15 at 18:03
  • Sorry, but without more information about what you're trying to achieve I really can't help you any more. Your stated goal sounds incredibly simple, but your formula is rather complex. Assuming none of the answers below help you, if you edited your post to include a sample of your input and expected output that would be helpful. – Kyle Nov 03 '15 at 18:40
  • I detailed my question a little bit more. – Cain Nuke Nov 03 '15 at 19:06

3 Answers3

0

This looks like it works

=INDEX(B:B, SUMPRODUCT((B:B-D:D*1.01=MAX(B:B-D:D*1.01))*ROW(B:B)))&"-"&INDEX(D:D, SUMPRODUCT((B:B-D:D*1.01=MAX(B:B-D:D*1.01))*ROW(B:B)))

If you need to exclude the first cell this answer says that the best way is to specify the cell numbers manually (e.g. B2:B1048576)

Community
  • 1
Marie
  • 169
  • 7
  • Referencing entire columns in SUMPRODUCT is a disastrous idea. That formula is being forced to calculate more than 10 MILLION cells, an absolutely astonishing number for a single formula. – XOR LX Nov 03 '15 at 15:29
  • Oh I see. Sorry, I am not very familiar with Excel, I assumed it would have just optimized away the empty cells before any calculation takes place. – Marie Nov 03 '15 at 15:34
  • Unfortunately not. COUNTIF(S)/SUMIF(S) do, though for whatever reason the other array-processing functions, i.e. SUMPRODUCT, AGGREGATE and any construction requiring CSE, do not. – XOR LX Nov 03 '15 at 15:35
  • In other words, it cant be done with this formula? What if I specify a smaller number? Such as 1000? – Cain Nuke Nov 03 '15 at 18:05
  • I dont think that means it cannot be done, just that it is very computationally intensive e.g. might run very slowly under some circumstances. Specifying a smaller row number would resolve that. – Marie Nov 03 '15 at 19:24
  • I did so but still doesnt work because seems like it needs all the cells in range to contain data. – Cain Nuke Nov 04 '15 at 01:25
0

Were you aware that your formula would fail if there happened to be more than one row in your data which shared the maximum value?

The idea of implementing one of the set-ups for determining the last-used row in your data as given in the link provided by Kyle is a sensible one.

However, you might also wish to consider this alternative, shorter construction, which will work even if some of the cells being referenced are blank, though with the caveat that none of the results from the part:

B2:B100-D2:D100*1.01

are negative.

=LOOKUP(1,0/FREQUENCY(0,1/(1+((B2:B100-D2:D100*1.01)))),B2:B100&" - "&D2:D100)

Obviously you can amend the upper row being referenced here, though be sure not to make it too arbitrarily large. Such constructions (as well as SUMPRODUCT, AGGREGATE and any set-up requiring CSE) calculate over all cells passed to them, whether technically beyond the last-used cells in those ranges or not.

Regards

XOR LX
  • 1,137
  • 6
  • 7
0

Borrowing liberally from the answer I linked in the comments... Open up the name manager (ctrl+F3) and define two names. Define range1 as:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH(9.9E+307,Sheet1!$B:$B,1))

and range2 as 

=Sheet1!$D$2:INDEX(Sheet1!$D:$D,MATCH(9.9E+307,Sheet1!$D:$D,1))

Then you can use your original formula, with the ranges replaced with the named ranges you just created:

=INDEX(range1,SUMPRODUCT((range1-range2*1.01=MAX(range1-range2*1.01))*ROW(range1))-ROW(B1))&" - "&INDEX(range2,SUMPRODUCT((range1-range2*1.01=MAX(range1-range2*1.01))*ROW(range1))-ROW(B1))

Bonus formula:

=INDEX(range1,MATCH(MAX(range1-range2),range1-range2,0))&" - "&INDEX(range2,MATCH(MAX(range1-range2),range1-range2,0))

Note that this is an array formula, and must be confirmed with ctrl+Shift+Enter.

Kyle
  • 2,406
  • 2
  • 11
  • 12
  • Great solution, thank you. I just want to ask you what does the 9.9E+307 mean? – Cain Nuke Nov 04 '15 at 12:45
  • @CainNuke It is 9.9*10^307. I believe it's the largest number Excel can represent via scientific notation. It's used in the `MATCH()` function to find any numbers, since your soccer scores probably will never get that high. – Kyle Nov 04 '15 at 14:06