Remember Me? September 1st 20, 03:31 AM posted to microsoft.public.excel.programming
 [email protected] external usenet poster First recorded activity by ExcelBanter: Aug 2020 Posts: 9 Array formula for lookup a max value cost too much time

Dear Excel friends,

Still struckling with a formula which cost a lot of time to run because of the amount of records (appr 50.000). When runs 'calculate' it cost many many minutes that I have to wait I'm using below formula based on array (Ctrl+Shift Enter)

{=MAX(IF(I:I=I2;Y:Y))}

Because it reports a zero when nothing found in column Y it's changed to

{=IF(MAX(IF(I:I=I2;Y:Y))=0;"";MAX(IF(I:I=I2;Y:Y))) }

Both formulas spend too much time when calculate.
Because I'm using this type of formula also in other columns you understand that this way of doing it is a lot of 'calculation waiting time'.

Is there a faster way for this formula ? (Another formula or VBA code).

Regards, Johan September 1st 20, 06:20 PM posted to microsoft.public.excel.programming
 Claus Busch external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,802 Array formula for lookup a max value cost too much time

Hi Johan,

Am Mon, 31 Aug 2020 19:31:04 -0700 (PDT) schrieb :

{=IF(MAX(IF(I:I=I2;Y:Y))=0;"";MAX(IF(I:I=I2;Y:Y))) }

Both formulas spend too much time when calculate.

I tried following formula with more than 50000 values and it did it in a
=IF(COUNTIF(I:I,I2)=0,"",MAXIFS(Y:Y,I:I,I2))

Regards
Claus B.
--
Windows10
Office 2016 September 1st 20, 06:45 PM posted to microsoft.public.excel.programming
 [email protected] external usenet poster First recorded activity by ExcelBanter: Aug 2020 Posts: 9 Array formula for lookup a max value cost too much time

Amazing =IF(MAXIFS(Y:Y;I:I;I2)=0;"";MAXIFS(Y:Y;I:I;I2))

9sec calculate time for 50.000 records.

THANKS !! September 1st 20, 06:52 PM posted to microsoft.public.excel.programming
 Claus Busch external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,802 Array formula for lookup a max value cost too much time

Hi Johan,

Am Tue, 1 Sep 2020 10:45:14 -0700 (PDT) schrieb :

Amazing =IF(MAXIFS(Y:Y;I:I;I2)=0;"";MAXIFS(Y:Y;I:I;I2))

9sec calculate time for 50.000 records.

if you change the first MAXIFS to COUNTIF it is a little bit faster:

=IF(COUNTIF(I:I,I2)=0,"",MAXIFS(Y:Y,I:I,I2))

Regards
Claus B.
--
Windows10
Office 2016 September 2nd 20, 04:35 AM posted to microsoft.public.excel.programming
 [email protected] external usenet poster First recorded activity by ExcelBanter: Aug 2020 Posts: 9 Array formula for lookup a max value cost too much time

Dear Claus,

Thanks remarked solution.
The problem is that when there is no value in column YY then it results with COUNTIF in 0 and with MAXIFS in "" (empty).

Is there also a formula solution for regards, Johan September 2nd 20, 08:16 PM posted to microsoft.public.excel.programming
 Claus Busch external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,802 Array formula for lookup a max value cost too much time

Hi Johan,

Am Tue, 1 Sep 2020 20:35:39 -0700 (PDT) schrieb :

The problem is that when there is no value in column YY then it results with COUNTIF in 0 and with MAXIFS in "" (empty).

try:
=IF(COUNTIFS(I:I,I2,Y:Y,"<")=0,"",MAXIFS(Y:Y,I:I, I2))

Regards
Claus B.
--
Windows10
Office 2016

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post gvm Charts and Charting in Excel 0 April 28th 10 02:10 PM Mark Gaipo Excel Worksheet Functions 1 October 15th 09 06:59 PM CJ-22 Excel Worksheet Functions 6 February 8th 06 06:45 PM SueDot Excel Programming 4 April 18th 05 06:04 PM Sam via OfficeKB.com Excel Worksheet Functions 2 February 5th 05 10:33 PM

All times are GMT +1. The time now is 02:58 PM. Copyright ©2004-2020 ExcelBanter.