Hey experts
I am doing a Ranking for non-FIFA football teams and thought Excel is the right solution for that.
For each team I have 2 sheets, one with the matches and one with all Rankings (in points, not positions) from 1.1.1900 until 31.12.2020
This way I can easily add matches and use search the opponents ranking of the match day, which I need to calculate the points each team of the match receives.
Well, this monster grew to big now. When I try to safe it I get the "Not enough Ressources" warning and saving stops.
I already changes calculations to manual and this way I can still work proper in the sheet (but I cannot safe the changes).
And I also allowed Excel to use all my 4 processor cores. That does not really help me, though.
I am using Excel 2010 in a 64-bit version on a Lenovo Thinkpad L412.
Here are the main formulas I use:
=INDEX(Factors!$B$4:$H$9;MATCH(Padania!G4;Factors!$A$4:$A$9;0);MATCH(J4;Factors!$B$3:$H$3;0))
=INDEX(INDIRECT("'"&C7&" Rankings'!$C:$C");MATCH(P7;INDIRECT("'"&C7&" Rankings'!$A:$A");0))
Those two (above) formulas are use for for every match, so ~200 times all over the sheet
=LOOKUP(2;1/(C4:C100000<>"");C4:C100000)
This one (above) is used to find the latest ranking in the historic rankings sheet. So I need this once for each team (~20 times for now)
=IF(ISNUMBER(MATCH(A3;Padania!B:B;0))=TRUE;INDEX(Padania!$W:$W;LOOKUP($A3;Padania!$B:$B;0);Padania!$W:$W);C2)
=IF(ISNUMBER(MATCH(A4;Padania!B:B;0))=TRUE;B3+1;B3)
The 2 (above) formulas are used for each date between 1.1.1900 and 31.12.2020. So this formula is used ~45000 times for each team, which means I need it ~1,000,000 already (number of teams is still growing, so I'd need it more often later).
Is there any chance to speed my sheet up ? I am quite new to Excel, but I am a mathematician and thus open for any VBA or formula solution
Best wishes and thanks loads in advance.
I put the xlxs file to a dropbox, so if anybody needs it for troubleshooting, just send me a PN.
Edit: Writing down my problem I just realized a first formula I can erase. This one is gone in the whole sheet now:
=LOOKUP(2;1/(C4:C100000<>"");C4:C100000)
The "end" of my list is always the same, so I just copy the last entry now, which seems smarter to me.
Bookmarks