Reason for slowness in excel (6k rows data only)

[ashish mehra]

Hi,

I am working on 6k rows of data https://onedrive.live.com/redir?resid=DCEA2A6B978AD1A5!313 using

=INDEX($B$7:$R$6589,$AC7,COLUMNS($AD$6:AD6))

& when I try to enclose the formula with IFERROR then excel takes a hell lot of time (about 20-30 minutes).

I am not able to figure out the reason, can someone look at the file & let the excel community what measures can be taken to avoid.

Maybe I am doing something wrong.

Any help would be appreciated.

Regards,
AM

 

[Syedali]

Hi,
I am unable to download your excel file. Kindly attach it.

 

[ashish mehra]

Hi Sydeali,

The file size is over 2 MB it does not allow me to upload.

However, I have checked & was able to download the file. You need to click on File menu & then save as Download a Copy.






Regards,
AM

 

[Somendra Misra]

Hi Ashish,

Can you check the performance of this file.

https://www.dropbox.com/s/9plo23kppo2i4dz/Dropdown excel dashboard.xlsx?dl=0

EDIT: Change the calculation mode to automatic, I had made it manual.
Regards,

 

[ashish mehra]

Sure

 

[ashish mehra]

Yes it works fine, no waiting time.

What changes you have made?

BTW, I am not able to view my dropdown menu.

Regards,
AM

 

[Somendra Misra]

@ashish mehra

I was also not able to see the dropdown .

You need to make one change the formula =IF(ROWS() > =

I had used >= , use >, download the new version.

Now first discuss, in column AB you entered whole as a array, I eliminated it and used SMALL function. Than used COUNT to get the no. of records, so if rows exceed the no. of records it should show blank.

The benefit of this method over IFERROR is that the calculation of ROWS is much smaller than evaluating whole INDEX function in IFERROR for whole range.

Regards,

 

[ashish mehra]

Hi SM,

In column AB you have also used array function

{=SMALL(IF($H$7:$H$6589=$AD$2,ROW($H$7:$H$6589)-ROW($H$7)+1),ROWS(AB$7:AB7))}

as compared to

{=IF($H$7:$H$6589=$AD$2,$AA$7:$AA$6589,"")}

I believe the choice of functions does the trick.

I guess the reason for not showing dropdown menu is related with Onedrive features, we have to look for an alternative for sharing files.

Regards,
AM

 

[NARAYANK991]

Hi Ashish ,

See this file for an alternative method.

https://www.dropbox.com/s/t8xnl0q68xpvj06/Dropdown excel dashboard.xlsx?dl=0

Of course , the ideal way would be to use an Advanced Filter in VBA , which would be instantaneous.

Narayan

 

[ashish mehra]

Hi Narayan,

Advance Filter sounds simple solution. Let me have a go at it.

Thanks for your time.

Regards,
AM

 

[NARAYANK991]

Hi Ashish ,

The reason for the slowness when you include IFERROR is that even when the result is an error value , the complicated compute-intensive formula still has to be evaluated ; thus , if your valid results are around 1250 , for the remaining 5250 rows , all of the cells will need this formula to be evaluated ; if you have around 17 columns , that comes to more than 85000 formula evaluations , which is going to take time.

If you instead calculate that there are only 1250 valid results , and use an IF statement so that if the row number is more than 1250 , then the formula does not need to be evaluated , you are saving on these 85000+ formula evaluations.

See this link for more details :

http://superuser.com/questions/8127...l-multiple-corresponding-values/812848#812848

Narayan

 

[ashish mehra]

Thanks Narayan,

You explained it well & now I understand the reason of slowness. This was again a new learning.

To get fast results I have to rely on vba.

I got the answer with Advance Filter, here is the attached file.

Regards,
AM