Excel simplify formula

Wries · #1 05-07-2014, 05:35 AM

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&"'")

the red refers to the revenue, margin ... depending on the cell the formula is in
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

now i need to recode that to dynamic one that will create an array output with sequential integer numbers from 0 till userinput
So far no success

Please help.

Thanks,
Kind regards,
Wries

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

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