Excel Performance Issues

[Stoner7Matt]

I have a table with values from A2:Y65000 and the Z2:AG6500 (formulas). I need this information in a pivot tables. I've listed each of the formulas below.

I need some advice of what I can do to process this information quickly. Each time I add data or refresh it takes 5 min to calculate. Is it faster and more efficient to add calculated columns in the pivot table? Any advice on how to approach this would be greatly appreciated.

Here's what i've tried...

FORMULAS
Z : LEFT(T1,3)&" to "&LEFT(X1,3) - Concatenates First 3 of origin zip & first 3 of destination zip
AA : UPPER(R1)&" to "&UPPER(V1) - Concatenates Origin City & Dest City
AB : S1&" to "&W1 - Concateneates Origin State & Dest State
AC : IFERROR(INDEX(Modes!$B:$B,MATCH('Transport - Lane Source Data'!H1,Modes!$A:$A,0)),"No Mode") - Classifies mode of transportation based on table in another sheet

AD : IFERROR(K1/J1,0) - Calculates Per Mile Rate
AE : COUNTIF($Z:$Z,$Z1)/(YEAR(TODAY())/52) - Calculates Frequency (Count) of Shipment in 52 week year based on Zip
AF : COUNTIF($AA:$AA,$AA1)/(YEAR(TODAY())/52) - Calculates Frequency (Count) of Shipment in 52 week year based on City
AG : COUNTIF($AB:$AB,$AB1)/(YEAR(TODAY())/52) - Calculates Frequency (Count) of Shipment in 52 week year based on State


1. VBA script to create/run formulas, then the same script pastes values after each column is run. AD - AG seem to slow this down tremendously.

2. Run formulas in a separate spreadsheet and paste values in. (Not ideal for the end-user)

 

[RoryA]

I'd start by putting:
=YEAR(TODAY())/52
in a cell and referring to that one cell in your formulas in AE:AG. TODAY() is a volatile formula and you really don't want to be repeating it.