Excel formula not working with column references

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.

• This answer might be helpful superuser.com/a/982611/457808. – Kyle Nov 3 '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 3 '15 at 18:03
• I detailed my question a little bit more. – Cain Nuke Nov 3 '15 at 19:06

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.

• Great solution, thank you. I just want to ask you what does the 9.9E+307 mean? – Cain Nuke Nov 4 '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 4 '15 at 14:06

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`)

• 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 3 '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 3 '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 3 '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 3 '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 3 '15 at 19:24

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

• I tried your formula but all I get is a hyphen with blanks. Could you please tell me about its construction? – Cain Nuke Nov 3 '15 at 18:02
• What was in the ranges B2:B100 and D2:D100 when you tested? – XOR LX Nov 3 '15 at 19:26
• The table I posted above below my question. – Cain Nuke Nov 4 '15 at 1:24