Slow file using index/match calculations

[espinozr]

Hello,

I have an excel file with 3 tables (10 columns x 1600 rows each), where I do an index/match search for each column (15K values in total per table).
Although the tables are big, they don't strike me as massive and yet the file is extremely slow to run. Here is a screenshot of the calculations:

Basically for each date, I need to check whether is the 1st, 2nd, 3rd, etc installation in the upper table (W3:W7). I have two additional tables with similar formulas.
So far I am copying and hard pasting the values for the periods I’ve already calculated, but I was wondering if is there any other way of running these calculations more efficiently?

Thanks,

 

[pjmorris]

There's actually quite a lot of work so perhaps its not a surprise its slow.

But the main thing is I can't see why you're making it so complicated. The date in column W appears to have no impact, other than making the MATCH look for 'something' You would be as well to simply count the number of entries in the column above: replace your entire formula with '=COUNT(AA$3:AA$7)'.

 

[espinozr]

There's actually quite a lot of work so perhaps its not a surprise its slow.

But the main thing is I can't see why you're making it so complicated. The date in column W appears to have no impact, other than making the MATCH look for 'something' You would be as well to simply count the number of entries in the column above: replace your entire formula with '=COUNT(AA$3:AA$7)'.

Hi Peter, thanks for your reply.

I'm probably missing something, but "CountA" will only give me the total for all the dates.

What I need is to show the number of installations based on the dates in the upper table. For example, for "B", my table will show 1 for all the dates until 15/12/2019, then "2" between 16/12/2019 and 17/01/2022, and then 3 for all the dates after 18/01/2022.

 

[pjmorris]

Ah, that makes sense. I'd try

=IFERROR(COUNTIF($X3:$X7,"<="&W10),0)

as that ought to be quicker. (you might need to adjust the cell references).

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[espinozr]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I'm using Office 365 (windows). Profile updated.

 

[Fluff]

Thank's for that.
Did you try the formula in post#4?
another option is

=MAXIFS($W$3:$W$7,X$3:X$7,"<="&$W11)

 

[espinozr]

Both formulas work, but both are very similar in terms of time. The first time I copied the formula in the whole range, both methods took ~42 seconds, and only 3 seconds after clicking "calculate now" for a second time.
I usually use this spreadsheet in manual (it was very slow), I'll use it with automatic calculations with both methods to see if there is a difference. It does seem faster.

Thanks four your help

 

[Fluff]

Glad we could help & thanks for the feedback.