Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 1st 20, 03:31 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2020
Posts: 9
Default 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


  #2   Report Post  
Old September 1st 20, 06:20 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,802
Default 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
blink:
=IF(COUNTIF(I:I,I2)=0,"",MAXIFS(Y:Y,I:I,I2))


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Old September 1st 20, 06:45 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2020
Posts: 9
Default 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 !!
  #4   Report Post  
Old September 1st 20, 06:52 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,802
Default 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
  #5   Report Post  
Old September 2nd 20, 04:35 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2020
Posts: 9
Default 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



  #6   Report Post  
Old September 2nd 20, 08:16 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,802
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating a total cost chart where a component cost is a step cost gvm Charts and Charting in Excel 0 April 28th 10 02:10 PM
Nested Array Formula to Determine Average cost Per Mile in 100 mileincrements Mark Gaipo Excel Worksheet Functions 1 October 15th 09 06:59 PM
Array formula lookup CJ-22 Excel Worksheet Functions 6 February 8th 06 06:45 PM
An Array Lookup Formula SueDot Excel Programming 4 April 18th 05 06:04 PM
Two-Way Lookup Array Formula 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.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017