New features in GS-Calc 20.2:

**Replacing formulas automatically in worksheets.**

GS-Calc 20.2 enables you to (“mass-”) replace formulas automatically in worksheets. You can use this for example, to:

- instantly mass-modify/add/remove functions in formulas or some parameters/arguments used by functions in large workbooks without the risk of making mistakes in long formulas,
- replace existing functions with your own ones,
- replace some Excel-specific functions from the Excel latest version with GS-Calc functions,
- replace in xlsx files some recently added Excel functions that are not implement in e.g. Excel 2019,
- in general: converting formulas from any spreadsheet software using its own extensions.

To replace any function(s) in all your formulas instantly, use the “Tools > Convert Function in Formulas” command.

You can easily replace a given function by any other functions or expressions that uses some or all of the original parameters in any order, embed them in any other functions or expressions, mix this with cell references

or constant values etc.

In the first edit field you need to specify how the function look like in formulas.

Replace actual parameters with subsequent letters a…z. In real formulas in your worksheet these parameters can be any expressions and nested functions, for example:

- VLOOKUP(a, b, c, d, e, f)
- TRIM(a)
- IF(a, b, c)
- MATCH(a, b, c)

In the second edit field you must specify the expression that will replace that function, for example:

- MY_VLOOKUP(abs(a) + 1, b, b100:b1000, e, d, 2) + 1
- TRIM(a & " of" & d10)
- IF(a, b, IF(c < b, b, c)) * 2
- MATCH(a, b, 0, 1, 1)

After clicking “OK”, all found formulas will be updated. You can specify whether these changes are to be

performed for the current selection only, for the entire worksheet or for all worksheets in the current workbook.

If the replacing expression includes new cell references and you want them to updated in subsequent cells same as when

e.g. when filling cells, check the “Update relative cell references” check box.

Updating is fast. Millions of formulas can be changed instantly.

GS-Calc displays information on how many formulas has been modified.

Besides changing GS-Calc worksheets you use this procedure to change Excel *.xlsx data.

For example, if you have an Excel worksheet using the new XMATCH or XLOOKUP Excel formula and have Excel 2019

that doesn’t have these functions. For example, to convert/replace

XMATCH(a, b, c, d)

You can use:

- For vertical binary searching (where COLS(b)=1):

MATCH(a, b, c, IF((d)=2, 8, IF((f)=-2, 16, IF((c)=-1, 512, 0))))- For the classic linear searching returning that last match:

MATCH(a, b, c, IF((c)=-1, 512, 0)) - For the classic linear searching:

MATCH(a, b, c)

To convert/replace

- For the classic linear searching returning that last match:

XLOOKUP(a, b, c, d, e, f)

You can use a construction similar to the following one:

- For vertical binary searching (where COLS(b)=1):

INDEX(c, MATCH(a, b, e, IF((f)=2, 8, IF((f)=-1, 512, 256))), )

**Note:**

GS-Calc match(), vlookup() functions offer a much wider parameter selection and thus much better performance

than the X- Excel counterparts.

For example, even if a given range is not sorted, you can still perform fast binary searching if you specify

SEARCH::AutoSort (or 1024)

in the “search mode” parameters. GS-Calc will perform the required sorting internally

during the 1st update and will keep the sorting indices (an unlimited number of them)

up to date. This may results in thousands of times faster updating.

In addition to the “find first” or “find last” match modes you can also explicitly

specify which matching occurrence should be returned.

## https://citadel5.com/help/gscalc/convert_dlg.png

To simplify performing multiple conversions, you can define scripts with various

conversion rules same as above. To do this, use the “Tools > Function Conversion Scripts”

command