• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Slow Calculation

rahulshewale1

Active Member
Dear sir,

PFA Spreadsheet.


Excel is taking so much time for calculation

Please suggest.

Or

Help to write VBA code .


Regard
Rahul shewale
 

Attachments

  • Sample Dataset for forum.xlsb
    868.8 KB · Views: 9
Hi ,

Are you talking about the recalculation time ?

What should we change in the worksheet to find out the recalculation time ?

Just pressing F9 does not show any problem , since the recalculation happens within a couple of seconds.

Narayan
 
@NARAYANK991 sir ,


Thank you for reply

I have uploaded few row of database but real database in around 25 thousand rows ,

It is taking too much time

How you please provide vba code for sumifs calculation

I will be great ful

Regard
Rahul shewale
 
Hi ,

I am not sure that using VBA will change anything.

From what I can see, your SUMIFS formulae are in only 4 columns , U , V , Y and AA.

In column Y , I don't see why the SUMIFS calculation should happen if there is no data in the column X.

Similarly , in column AA , I don't see why the SUMIFS calculation should happen if there is no data in the column Z.

These two column formulae can therefore be rewritten as :

Column Y : =IF(X5 = "", 0, SUMIFS($X$5:$X$6880,$N$5:$N$6880,N5,$D$5:$D$6880,D5,$B$5:$B$6880,1))

Column AA : =IF(Z5 = "", 0, SUMIFS($Z$5:$Z$6880,$N$5:$N$6880,N5,$D$5:$D$6880,D5,$B$5:$B$6880,1))


In column U , instead of having an additional IF condition test , I tried using this formula :

=SUMIFS($T$5:$T$6880,$N$5:$N$6880,N5,$D$5:$D$6880,D5, $B$5:$B$6880,1)

From what I can see , most of the data relates to individual parties in Pune ; where there are groups involved , the data is very little compared to the overall data size , say 50 rows in 7000.

Going by this , can you not separate the data into 2 worksheets , one containing data only for individual parties in Pune , and the other for groups ?

This way each worksheet will have to deal with a lesser amount of data.

Narayan
 
Back
Top