Category Archives: Calculation

Python in Excel: Python User Defined Functions

Posted on November 6, 2023 by fastexcel

This post explores using Python in Excel to create Python User Defined Functions. The example used is a function to calculate Gini coefficients (see https://fastexcel.wordpress.com/2011/05/21/fast-gini/). Defining the UDF Python UDFs live in the workbook global Python space and must be … Continue reading →

Posted in Calculation, Excel, Python, UDF | Tagged , , , | Leave a comment

Python in Excel: Controlling Python Calculation

Posted on November 2, 2023 by fastexcel

Because Python calculations can be slow it is worth looking at ways of controlling Python calculation. Partial Calculation The calculation setting that used to be called “Automatic except Data Tables” has been renamed to “Partial”. Partial Calculation works like Automatic … Continue reading →

Posted in Calculation, Excel, Python, VBA | Tagged , , , | Leave a comment

Python in Excel: How do Excel and Python Formulas Work Together?

Posted on November 1, 2023 by fastexcel

In the previous post I showed how Python and Excel calculation methods are completely different: So when Excel formulas and Python formulas depend on one another how does this all work? Excel->Python->Excel->Python Lets look at a simple example of inter-dependent … Continue reading →

Posted in Calculation, Excel, Python | Tagged , | Leave a comment

Python in Excel: PY Calculation, Globals & Co-Volatility

Posted on November 1, 2023 by fastexcel

Python Calculation Python in Excel calculates all the PY cells in a workbook sheet by sheet, and row by row, starting at the top left PY cell. If you break this calculation sequence rule, by for instance moving cell E5 … Continue reading →

Posted in Calculation, Excel, Python | Tagged , , , | Leave a comment

Python in Excel: Round-Tripping Gotchas

Posted on October 24, 2023 by fastexcel

Python is a full-blown programming language and so supports a rich variety of datatypes.So what happens when you move data from Excel to Python and back to Excel (Round-Trip)? Excel Datatypes It is important to distinguish between formatted Excel values … Continue reading →

Posted in Calculation, Python | Tagged , | Leave a comment

Manual Calculation No Longer Viral!

Posted on March 14, 2023 by fastexcel

Excel has 3 basic calculation modes: Automatic, Manual and Automatic except Tables. But an Excel session can currently only have one Calculation mode (unless you are using FastExcel which allows open workbooks and their worksheets to have different Calculation Modes). … Continue reading →

Posted in Calculation | Tagged , | Leave a comment

Only Calculating the Active Workbook

Posted on March 1, 2023 by fastexcel

When you have more than one workbook open Excel has always recalculated all of the workbooks at each recalculation. That can be very painful if one or more of the workbooks are slow to calculate. Now the Excel Dev Team … Continue reading →

Posted in Calculation, Excel | Tagged | Leave a comment

Excel Screen-updating Performance Comparison.

Posted on January 14, 2023 by fastexcel

The time that Excel takes to refresh the screen display can be a significant factor in overall processing speed. Things that affect screen updating Time My benchmarking workbook The workbook contains one sheet (Sheet1) with 4895 formulas and 1000 constants. … Continue reading →

Posted in Calculation | Tagged , | Leave a comment

Short-circuiting in Array Formulas

Posted on January 6, 2023 by fastexcel

The previous post demonstrated that IF and CHOOSE short-circuit but IFS and SWITCH do not. But following up a hint from Bill Wu that IF behaves differently in array formulas I decided to check whether it still short-circuits.(IF in array … Continue reading →

Posted in arrays, Calculation, Dynamic Arrays, Excel, UDF | Tagged | 5 Comments

Short-circuiting Excel Formulas: IF, CHOOSE, IFS and SWITCH

Posted on January 3, 2023 by fastexcel

What is formula short-circuiting? Short-circuiting occurs in Excel formulas when an IF statement only evaluates the True part of the IF and skips the False part of the IF. For example in this formula: Because 1>2 evaluates to False the … Continue reading →

Posted in Calculation, Excel | Tagged , , | 5 Comments