#1
|
|||
|
|||
Excel simplify formula - array of sequential numbers
Hello All,
I have 96 cells with the following formula which causes slow excel perfomance. I am looking for a way how to simplify it however i am not sure what would be the most CPU consuming part: Code:
=SUMPRODUCT(GETPIVOTDATA("Sum of "&$A$7,'2014'!$A$12,"BookingDate",DATE(2013,10, 1)+7*INDIRECT("AUX!B1:B"&Departure!$B$3),"DepartureDate",DATE(2013,10,1)))+GETPIVOTDATA('2013'!$A$12,"DepartureDate['"&TEXT(B6, "mmm-yy")&"';Sum] 'Sum of "&$A$7&"'") yellow refers to month green is where i suppose the problem may be basically i needed an dynamic array of sequential numbers depending on the user selected (cell B3) week so the array would be {1, 2, ..., Cell B3} the formula therefore call for cell values from B1 to B"week#" where are numbers from 0 to "week#"-1 now i have found out that it would be easier to refer to the row number rather than a cell value (which i assume will speed up the process) I was however thinking about creating a public function that will return such array This one works (tested with the above formula) Code:
Function MYARRAY(x As Integer) MYARRAY = Array(0, 1, 2, 3, 4) End Function So far no success Please help. Thanks, Kind regards, Wries |
#2
|
||||
|
||||
I thought maybe someone else would have a better answer, Wries, so I waited. But no one else has come up with a response yet, so I'll take a whack at it.
96 cells? That doesn't sound like very many; it isn't clear to me how that few cells could be slowing things down. How do you define "slow"? How much delay is there between <F9> and the end of the recalculation? I'm also not convinced that INDIRECT is the probable cause. I used to use it quite a bit—I've found some better ways since, or ways at least that I think are better—and although I thought it would slow things down, I didn't usually find it so. So what do you count as "slow"? |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Creating a TOC with sequential letters instead of page numbers at the right margin | shansen | Word | 1 | 03-11-2014 12:43 PM |
Convert String Array to Integer Array from a User Input? | tinfanide | Excel Programming | 4 | 12-26-2012 08:56 PM |
Sequential document numbering excel'97 | Scottlambert | Excel | 1 | 05-11-2012 03:40 AM |
Complex array formula | andrei | Excel | 9 | 02-03-2012 03:40 AM |
How to count the frequency of data and also tally value from an array of Excel record | KIM SOLIS | Excel | 5 | 09-07-2011 09:01 AM |