# 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

• 16 KB Views: 11

#### 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).

#### Hui

##### Excel Ninja
Staff member
Why not remove the formulae
Do the sort
Then apply the formula

#### sivaprakasam

##### New Member

Can u use the formula through VBA?

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

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

• Chihiro

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

• NARAYANK991