Faster way of re-calculating cells in a sheet with 31685 rows of data

[jasonleewkd]

hi all, it takes about 20 secs to recalculate this bunch of data when I turn on/off the filters. Is there a faster formula to do this? Probably an array i suppose?


The formula in Column 'Fresh launch' reads

=IF(AND(J2=M2,M2>0),"Yes","No")


'

Year Month Quarter Project Name Street Name Developer Property Type Locality Total Number of Units in Project Cumulative Units Launched to-date Cumulative Units Sold to-date Cumulative Units Launched but Unsold Units Launched in the Month Fresh Launch Units Sold in the Month Median Price ($psf) # in the Month Number Sold By Price Range Lowest Price ($psf) # in the Month Highest Price ($psf) # in the Month

2007 6 2Q 3@PHILLIPS PHILLIPS AVENUE Sovereign Development Pte Ltd Non-Landed OCR 16 16 16 0 0 No 0 - - -

2007 6 2Q 38 AMBER AMBER ROAD Ho Brothers Investment Pte Ltd Non-Landed RCR 30 0 0 0 0 No 0 - - -

2007 6 2Q 3BHC BRIGHT HILL CRESCENT Manston Land Pte Ltd Strata-Landed RCR 11 11 11 0 0 No 0 - - -

2007 6 2Q 8 @ MOUNT SOPHIA MOUNT SOPHIA FCL Sophia Pte Ltd Non-Landed CCR 313 313 313 0 0 No 0 - - -

2007 6 2Q 8 @ STRATTON STRATTON GREEN Fairview Developments Pte Ltd Strata-Landed OCR 8 0 0 0 0 No 0 - - -

2007 6 2Q 8 NAPIER NAPIER ROAD Napier Properties Pte Ltd Non-Landed CCR 46 0 0 0 0 No 0 - - -

2007 6 2Q AQUENE JOO CHIAT PLACE/LORONG BANDANG Deneside Pte Ltd Non-Landed OCR 22 0 0 0 0 No 0 - - -

2007 6 2Q ARDMORE II ARDMORE PARK/ANDERSON ROAD Actbilt Pte Ltd Non-Landed CCR 118 118 118 0 0 No 1 3013 3013 3013

2007 6 2Q ASTOR LENGKONG EMPAT Astor Properties Pte Ltd Non-Landed OCR 55 55 48 7 0 No 3 640 479 755

'

 

[jasonleewkd]

I'm so sorry, here's the data presented properly

[pre]

Year	Month	Quarter	Project Name	Street Name	Developer	Property Type	Locality	Total Number of Units in Project	Cumulative Units Launched to-date	Cumulative Units Sold to-date	Cumulative Units Launched but Unsold	Units Launched in the Month	Fresh Launch	Units Sold in the Month	Median Price ($psf) # in the Month Number Sold By Price Range	Lowest Price ($psf) # in the Month	Highest Price ($psf) # in the Month
2007	6	2Q	3@PHILLIPS	PHILLIPS AVENUE	Sovereign Development Pte Ltd	Non-Landed	OCR	16	16	16	0	0	No	0	-	-	-
2007	6	2Q	38 AMBER	AMBER ROAD	Ho Brothers Investment Pte Ltd	Non-Landed	RCR	30	0	0	0	0	No	0	-	-	-
2007	6	2Q	3BHC	BRIGHT HILL CRESCENT	Manston Land Pte Ltd	Strata-Landed	RCR	11	11	11	0	0	No	0	-	-	-
2007	6	2Q	8 @ MOUNT SOPHIA	MOUNT SOPHIA	FCL Sophia Pte Ltd	Non-Landed	CCR	313	313	313	0	0	No	0	-	-	-
2007	6	2Q	8 @ STRATTON	STRATTON GREEN	Fairview Developments Pte Ltd	Strata-Landed	OCR	8	0	0	0	0	No	0	-	-	-
2007	6	2Q	8 NAPIER	NAPIER ROAD	Napier Properties Pte Ltd	Non-Landed	CCR	46	0	0	0	0	No	0	-	-	-
2007	6	2Q	AQUENE	JOO CHIAT PLACE/LORONG BANDANG	Deneside Pte Ltd	Non-Landed	OCR	22	0	0	0	0	No	0	-	-	-
2007	6	2Q	ARDMORE II	ARDMORE PARK/ANDERSON ROAD	Actbilt Pte Ltd	Non-Landed	CCR	118	118	118	0	0	No	1	3013	3013	3013
2007	6	2Q	ASTOR	LENGKONG EMPAT	Astor Properties Pte Ltd	Non-Landed	OCR	55	55	48	7	0	No	3	640	479	755

[/pre]

 

[Hui]

Jasonleewkd


It is highly unlikely that the large volume of data is causing the slow response

It is more likely that you have a heap of Sumproducts, Offsets or other Volatile functions

that are accessing lots of cells repeatedly.


Can you tell us is this data static or is it added to regularly?

Is it static or downloaded from a DB/SQL query etc

What other reports are already in place ?


You can simplify the formula by simply using:

=AND(J2=M2,M2>0)

This will return True or False and will be quicker as well


Are any of the existing fields calculations? Can they be replaced with fixed values


Can you share the file?