Replacing formulas automatically in worksheets |
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:
In the second edit field you must specify the expression that will replace that function, for example:
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
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
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.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
Scripts are just named sequences of conversions. You can add any number of them and each one
can contain any number of conversion definitions.