Array formula working too slow

Hello,

I am struggling with a formula, which works OK, but is not efficient for the large amount of data stored in the Excel sheet. The table I am using have thousands of rows and the workbook becomes very slow because of the calculations.

I was wondering if the formula I am using can be changed or modify to make it quicker. Maybe a macro will do the job as well.

I have attached a small version of the table where I have the formula. Columns in red have the formulas which I am explaining.

Hope someone can give me an idea about this. Any help would be much appreciated.

Thanks & Regards

Hi.
How many thousands of lines do you have ?
The array appears to be iterating through 1,048,576 lines, is this necessary at this stage of your work.
In the formula in column 'CA' I have just reduced the three occurrences of this figure to 10,485.
The calculation is the instant and at 104,857 is less than 1 second.
torachan.

Hi,

Thanks PaulM100 and torachan for your help!

I am trying the formula from PaulM100, but is still too slow processing. Excel is not responding when introducing the formula.

Table has over 350.000 rows

How did you reduced the formula?

Thanks & Regards

Hi
I suppose you want this with a helper column
In column CD try this array formula CD11 and copy down

Formula:

Please Login or Register  to view this content.

The new values for column CA are given by a normal formula

Formula:

Please Login or Register  to view this content.

The values in column CB don't need to be array formula.
See the file

In cell CA11 formula, reduce 1,048,576 to 500,000 (in the three instances) this will adequately cover your 350,000 lines.
Copy the formula in cell CA11, highlight the range to you are copying down to.
As you are dealing with an array formula the next step is IMPORTANT !
Whilst holding down Ctrl & Shift keys together - with cursor in highlighted area - select 'paste'.
If you have not copied arrays before practice on a copy of your worksheet.
Also noticed your actual cell values are not the same as your formatted/display values.
As standard Excel works with the actual cell value, this in turn has effect on speed, especially on older processors.
You could go to your Excel settings >Options> Advanced> When calc this Workbook >Set precision as displayed to further increase speed.
torachan.

Dear all,

Thanks for the help, much appreciated!

I finally used solution from José Augusto, works very good! Workbook runs much more smooth.

I will close this thread.

Thanks!

Although this thread has been closed by O.P.
There is a salient lesson in this thread for all of us.
Congratulation to 'avk' for his observation that we ALL missed.
After checking this I was amazed at the time Excel took to process nothing when running an array.
Never toooo old to learn !!!!
torachan

Thanks kvsrinivasamurthy!