Environmental Formulas

Two formula types turn the three criteria into a number and then into a band.

The two types

Type	Produces	May reference
Environmental score	The numeric score	[S] [O] [Y]
Environmental value	The band number	[S] [O] [Y] [CS]

Token	Is
[S]	Severity
[O]	Probability
[Y]	Legal status
[CS]	The resulting score

A band formula may read [CS]; a score formula may not — it is the thing the score formula exists to produce, and the system refuses to save a score expression that names it.

A band expression may also name the three criteria directly, because forcing the top band from severity alone is a common requirement and cannot be expressed from the score.

Fields

• Code / Name — the formula's identity.

• Formula type — score or value. Set this first; it decides which tokens the editor accepts.

• Expression — the expression itself.

• Blank value default
  What an unanswered variable is worth. Empty (the default) means the formula refuses to score while any variable it names has no value. See Blank value default below.

• Active — one active formula per type. Activating one stands the previous one of that type down.

The shipped formulas

Code	Name	Type	Expression	Active
CFM00001	Multiplicative Environmental Score	Score	[S]*[O]*[Y]	Yes
CFM00002	Multiplicative Environmental Value	Value	IF([CS]>64:4:IF([CS]>27:3:IF([CS]>8:2:1)))	Yes
CFM00003	Additive Environmental Score	Score	[S]+[O]+[Y]	No
CFM00004	Additive Environmental Value	Value	IF([CS]>=12:4:IF([CS]>=9:3:IF([CS]>=6:2:1)))	No

The additive pair ships inactive because activating a formula stands the other one of its type down — seeding both pairs active would leave one formula of each type, chosen arbitrarily.

caution

Range compatibility is not checked, and the failure is silent.

Activate the additive score (range 3 to 15) while leaving the multiplicative bands in place, where significance starts at 28, and nothing will ever be significant. No error appears anywhere. The symptom is a register that reports no significant aspects, which reads like good news rather than a misconfiguration.

If you switch one formula, switch both. The formula screen carries this warning above the expression field.

Writing expressions

Expressions use spreadsheet syntax. Both IF(a:b:c) and IF(a,b,c) are accepted, and the usual functions are available: MAX, MIN, SUM, AVERAGE, ROUND and their relatives.

Tokens are bracketed so that the F inside IF( cannot be read as a variable.

Examples

A score that weights legal status double:

[S]*[O]*[Y]*2

A band that forces the top band whenever a hard legal limit applies, whatever the score:

IF([Y]>=5 : 4 : IF([CS]>64 : 4 : IF([CS]>27 : 3 : IF([CS]>8 : 2 : 1))))

note

That second example does by formula what a legal status level's makes significant flag does by definition — and the flag is usually the better tool, because it is visible on the level itself rather than buried in an expression. Use the formula where you need the band raised; use the flag where you need significance regardless of band.

Your own fields as variables

Any parametric field you have defined on the record this formula scores can be named in an expression, in brackets, exactly like the built-in variables:

[S]*[O]*[Y]*[site_factor]

Which of your fields are eligible:

Field type	Usable in a formula
Number, Decimal	Yes
Yes / No	Yes — counts as 1 or 0
Single select	Only if every option's value is a number or a yes/no
Everything else — text, dates, files, references, multi-select	No

A select over labels is a category, and multiplying by a category means nothing however tidy the arithmetic looks. The formula editor's palette lists the eligible fields beside the built-in variables, so you do not have to remember the key.

The score shown live on the assessment screen is worked out from the same fields as the stored score, so what you see while filling the form is what gets saved. See Parametric Fields for how to define one.

Blank value default

A blank field is not zero. By default, a formula that names a field with no value refuses to score at all, and the record keeps whatever it had. That is deliberate: an empty cell would evaluate as nought, and a score of nought reads as no risk rather than not answered yet.

That default is right when every variable is compulsory, and wrong when a parametric field is genuinely optional. So each formula carries its own setting:

• Blank value default — empty (the default)
  Refuse to score while any referenced variable has no value.

• Blank value default — a number
  Every unsupplied variable evaluates as that number. Set it to 1 for a multiplier that should have no effect when unanswered, or 0 for an addend.

One number applies to all of that formula's variables; it is not set per field. Zero is a legitimate choice — leaving the setting empty is what means "no default", not entering 0.

caution

Before setting a blank value default, be sure the answer it produces is one you would defend. "Treat blanks as 1" quietly turns an unanswered exposure factor into a full-weight one.

The formula editor

The expression is edited on a full page rather than in a modal, because the editor carries three things:

• A token palette of the variables this type may use, following the formula type field.
• A live check validating the expression as you type. A refusal mid-typing is expected.
• A try-it panel that evaluates the expression against values you supply, and for a band formula reports which band the result lands on, or warns that no band declares that number.

tip

Test the corners: all three criteria at 1, all three at 5, and the legal-status override on its own. A formula that behaves on 3, 3, 3 has told you nothing about either end of its range.

Import and export

Formula definitions export to Excel and re-load from a filled-in template. The expression is checked on the way in exactly as on the form, so one the editor refuses cannot be stored by uploading it instead.