Can somone speed up this formula?
 

Hi Group,

I got this formula of the NG, which returns the total
score of a team based on Manager Name in cell O1 (each
team occupies 20 rows). It works well but it has slowed
down, big time I might add, various Solver routines that I
use to work out best team etc.

Can someone suggest an alternative solution for working
out the team score or should I just let my P500 grind away
for the extra minute.

=IF(O1="","",(SUM(INDIRECT("Teams!I"&MATCH(O1,Team s!A:A,0)
&":I"&MATCH(O1,Teams!A:A,0)+19))))

Many thanks

Tony

Can somone speed up this formula?
 

Please tell us more about how your worksheet works.


---
Message posted from http://www.ExcelForum.com/

Can somone speed up this formula?
 

I think the array "SUM" function is the culprit. The other reference type functions could be too if you are referencing them to huge databases. You probably have a lot of them on your worksheet to slow down you system this much. I have 2 ideas, not sure how much they will help though:

1. Replacing the array "SUM" by "DSUM"
2. Consolidate some of your reference tables so you would only have to lookup something once instead of 2 MATCH's and an INDIRECT

Cheers,

"Tony" wrote:

Hi Group,

I got this formula of the NG, which returns the total
score of a team based on Manager Name in cell O1 (each
team occupies 20 rows). It works well but it has slowed
down, big time I might add, various Solver routines that I
use to work out best team etc.

Can someone suggest an alternative solution for working
out the team score or should I just let my P500 grind away
for the extra minute.

=IF(O1="","",(SUM(INDIRECT("Teams!I"&MATCH(O1,Team s!A:A,0)
&":I"&MATCH(O1,Teams!A:A,0)+19))))

Many thanks

Tony

Can somone speed up this formula?
 

Hi Berend,

I have 2 main worksheets (League & Teams).
The League sheet produces a League table based on teams
that are entered in a fantasy football league. These
teams are stored in Teams worksheet and each team begins
every 20th row, i.e A2, A22, A42. For example, my team
starts at A2, here is the first 3 lines of my team

A1 B C D E I
Manager Code Player Team Value Points
A2
Tony 7 Barthez France 5 2
111 Jiranek Czech 2.5 -1
115 Laursen Denmark 3 15

The formula I posted is located on the League worksheet in
cell P1 and returns the total points for each team from
the Teams worksheet (ColI).

O1 contains the following formula to return each manager
Name from the Teams worksheet

=IF(INDEX(Teams!A:A,ROW()*20-18)<"",INDEX(Teams!A:A,ROW()
*20-18),"")

HTH

Thanks

Tony


-----Original Message-----
Please tell us more about how your worksheet works.


---
Message posted from http://www.ExcelForum.com/

.