Lotus Transition Formula Evaluation Errors

Ron de bruin and Norman Harker, Last update 23-April-2005

Go to the Excel tips page

 

 

The Lotus Transition Formula Evaluation Setting

This setting can be found using: Tools > Options > Transition Tab
Check or Remove Check from "Transition formula evaluation"
 

 

The intention of including the Transition formula evaluation setting is to smooth transition to Excel of those used to using Lotus 1-2-3.
 


The Problem with Transition formula evaluation

1. A formula used elsewhere with no errors, produces an error when used in another workbook.
2. A formula used in a workbook was previously working OK and now starts to produce errors.

Examples

The following formula works OK with the default setting of Transition formula evaluation unchecked:
=DATE(2005,5,0)   Returns: >>>  Sat 30/Apr/2005

But with the Transition formula evaluation checked:
=DATE(2005,5,0)   Returns: >>>  #NUM!

Similary many formulas involving coerced text functions will evaluate differently:

With Transition formula evaluation unchecked:
=--RIGHT("A123",3)   Returns: >>>  123


With Transition formula evaluation checked:

=--RIGHT("A123",3)   Returns: >>>  0

 


Checking by Formula whether Transition formula evaluation is checked or unchecked:

With Transition formula evaluation unchecked:
ISERR("^1234"*1)   Returns: >>>   TRUE


With Transition formula evaluation checked:

ISERR("^1234"*1)   Returns: >>>   0


We can use the following to display an explicit error message:

=IF(ISERR("^1234"*1),"Lotus Transition formula evaluation is NOT enabled","Lotus Transition formula evaluation IS enabled")
Returns (Depending upon the setting: Lotus Transition formula evaluation IS enabled

OR: Lotus Transition formula evaluation is NOT enabled

Or we might use the following to return a 0 or 1 numeric return

=IF(ISERR("^1234"*1)=0,1,0)
Returns (Depending upon the setting: 1 if Lotus Transition Formula evaluation IS enabled
OR: 0 if Lotus Transition Formula evaluation Is NOT enabled



Adjusting Formulas for Potential of "Transition formula evaluation" Being Checked

We can adjust formulas to give an explicit error message when "Transition formula evaluation" is checked:
=IF(ISERR("^1234"*1)=0,"Use Tools > Options > Transition > Remove check from Transition formula evaluation",DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))
Example showing either last day of current month or error message:

Although it may be possible in some cases to design formulas to return correctly under both Transition formula evaluation settings,

the recommendation is to change the setting to the default.
 


Notes Regarding the Transition formulas evaluation setting

1. Any setting change only applies to the active worksheet. A change only affects the active worksheet.
2. The default status of the Transition formula evaluation is unchecked. Any new worksheet inserted will have the setting unchecked.
3. This default status is independent of the setting that may be adopted by the first workbook opened by excel unless you have changed

the defaults in the default worksheet or workbook (Sheet.xlt or Book.xlt) opened in the XLSTART folder.

 

If you find the setting checked in Book1 when you open Excel and (recommended) you want to open new workbooks with the setting unchecked or you

find the setting checked in every new worksheet that you insert then you must change the setting in your existing Book.xlt or Sheet.xlt in your

XLSTART folder.

 

1. If you find the setting checked in Book1 and (recommended) you want to open new workbooks with the setting unchecked:
a. File > Open > Open the Book.xlt in your XLSTART folder
b. Tools > Options > Transition Tab
c. Remove check from "Transition formula evaluation".
d. OK
e. Repeat steps b. c. and d. for each sheet in the workbook.
f.  File > Save As
g. Save as Type drop down: Select Template (*.xlt)
h. File name dialog box: Book
i.  Save in drop down: Navigate to your XLSTART folder
j.  Save (say yes to replace the existing file)
k. Close Excel
l.  Re-open Excel and check the setting in Tools > Options > Transition Tab
 


2. If you find the setting checked in every new worksheet that you insert and (recommended) you want to insert worksheets with the setting unchecked:

a. File > Open > Open the Sheet.xlt in your XLSTART folder
c. Tools > Options > Transition Tab
c. Make sure that the "Transition formula evaluation" checkbox is NOT checked
d. OK
e. File > Save As
f.  Save as Type drop down: Select Template (*.xlt)
g. File name dialog box: Sheet
h. Save in drop down: Navigate to your XLSTART folder
i.  Save (say yes to replace the existing file)
j.  Close Excel
k. Re-open Excel.
l.  Insert > Worksheet
n. Check the setting in Tools > Options > Transition Tab

 


3. For the avoidance of doubt, changes to the Personal workbook do not take precedence over the Book1 template.
 

 

 

Changing Transition formula evaluation with VBA

Here are some useful VBA subroutines to change the setting:
 

Public Sub EnableLotusFormulaEvaluation()
    ActiveSheet.TransitionExpEval = True
End Sub
 
Public Sub DisableLotusFormulaEvaluation()
    ActiveSheet.TransitionExpEval = False
End Sub
 
Public Sub ToggleLotusFormulaEvaluation()
    ActiveSheet.TransitionExpEval = Not ActiveSheet.TransitionExpEval
End Sub
 
 
Public Sub EnableLotusFormulaEvaluationAllSheets()
    Dim wkSht As Worksheet
    For Each wkSht In ActiveWorkbook.Worksheets
        wkSht.TransitionExpEval = True
    Next wkSht
    MsgBox "Transition formula evaluation Enabled"
End Sub
 
Public Sub DisableLotusFormulaEvaluationAllSheets()
    Dim wkSht As Worksheet
    For Each wkSht In ActiveWorkbook.Worksheets
        wkSht.TransitionExpEval = False
    Next wkSht
    MsgBox "Transition formula evaluation Disabled"
End Sub