Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can somone speed up this formula?
Please tell us more about how your worksheet works.
--- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Speed of array formula | Excel Worksheet Functions | |||
Calc Speed & Formula simplification | Excel Discussion (Misc queries) | |||
Worksheet speed with array formula | Excel Discussion (Misc queries) | |||
Speed up calculation of a worksheet that contains formula | Excel Worksheet Functions | |||
Formula recalculation speed | Excel Programming |