• 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.

formula apply to 200000 rows, excel is slow very

sivaprakasam

New Member
Hi

I have try to the formula in difference sheet with "summary" sheet

1st sort with condition column for "Distinct Count of Reservation" & "Distinct Count No.of Open Reservation"

formula is correct but that formula apply to 200000 rows, excel is slow very.

kindly provide alternative solution for fast calculation
 

Attachments

Chihiro

Excel Ninja
It's because of multiple dependency. Each down stream calculation is dependent on previous step, so it increases calculation time.

Depending on your Excel version. Best bet is to use Data model based Pivot Table (not necessarily PowerPivot).
 

NARAYANK991

Excel Ninja
Hi ,

It may well be that what the others have suggested will be the only way to resolve your problem.

However , before you try their suggestions , just see if this improves matters.

Your two formulae are :

1. =IF(COUNTIFS($A$3:A3,A3,$B$3:B3,B3)>1,0,1)

2. =IF(F3=1,COUNTIFS(E3,">0",D3,">0"),0)

Try replacing 1 by this :

=--(COUNTIFS($A$3:A3,A3,$B$3:B3,B3)=1)

Try replacing 2 by this :

=COUNTIFS(C3,">0",D3,0)*F3

Narayan
 

Peter Bartholomew

Well-Known Member
You indicate that your data is sorted by 'plant' and 'reservation'. Provided that is always true, your formula seeking distinct 'plant/reservation' pairs need only examine the immediate prior row for a match.
= 1 - (plant=priorPlant) * (reservation=priorRes)
Calling the result 'distinct' (the names are all single cell relative references) the 'open reservation' formula becomes
= distinct * (required>0) * (removed=0).

The first of these formulas uses the sort to reduce an order calculation to order n. As a rough guide, I would expect the resulting calculation to be about 10,000 times as fast.
 
Top