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
  • 189

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
  • 170
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
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,436