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:
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)
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

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"?