How to fix a slow excel calculation?

%3CLINGO-SUB%20id%3D%22lingo-sub-2654726%22%20slang%3D%22en-US%22%3EHow%20to%20fix%20a%20slow%20excel%20calculation%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2654726%22%20slang%3D%22en-US%22%3E%3CP%3EI%20made%20a%20calculator%20using%20excel2013%20for%20analyzing%20rainfall%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(input%20data%20%3D%20white%20cells%2C%20final%20output%20data%20%3D%20yellow%20cells)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20it%20is%20too%20slow%20for%20a%20calculation%20when%20i%20changed%20the%20old%20input%20data%20to%20new%20input%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehow%20do%20i%20make%20it%20better%3F%26nbsp%3Bis%20there%20anyone%20who%20can%20help%20me%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2654726%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2655128%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20fix%20a%20slow%20excel%20calculation%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2655128%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1129936%22%20target%3D%22_blank%22%3E%40Jisu_Kim%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20a%20minimum%20use%20dynamic%20ranges%20in%20formulas%2C%20e.g.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DSMALL(Z1%3AINDEX(Z%3AZ%2C%20COUNTA(B%3AB))%2CAB2)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Einstead%20of%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSMALL(Z%3AZ%2CAB2)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ein%20all%20other%20formulas%20as%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2657594%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20fix%20a%20slow%20excel%20calculation%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2657594%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1129936%22%20target%3D%22_blank%22%3E%40Jisu_Kim%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E800%2C000%20formulas%20is%20a%20bit%20to%20much%20for%20any%20workbook.%3C%2FP%3E%3CP%3EYou%20could%20do%20all%20the%20calculations%20in%20Power%20Query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I made a calculator using excel2013 for analyzing rainfall data.

 

(input data = white cells, final output data = yellow cells)

 

But it is too slow for a calculation when i changed the old input data to new input data.

 

how do i make it better? is there anyone who can help me?

3 Replies

@Jisu_Kim 

As a minimum use dynamic ranges in formulas, e.g.

=SMALL(Z1:INDEX(Z:Z, COUNTA(B:B)),AB2)

instead of

=SMALL(Z:Z,AB2)

in all other formulas as well.

@Jisu_Kim 

800,000 formulas is a bit to much for any workbook.

You could do all the calculations in Power Query.

 

@Sergei Baklan

Thank you for your reply.