BNG4.0 : The saga of a single cell
Oct '23 - symbol ~ replaced with the more explicit $self/
Implementation of the BNG4.0 spreadsheet calculator in XML and XQuery requires the translation of multiple formula expressed in Excel language. This is time-consuming process as shown in this example of one column of one table.
The formula of interest is in cell T12 of Sheet A-3 On-Site Habitat Enhancement. The heading is Distinctiveness Change.
I discovered I could copy the formula by double-clicking the cell [previously I'd tried to copy the text in the entry field but couldn't because it was locked]
=IF(E12="","",IF(AND(LEFT(O12,6)="Same d",I12>X12),"Error - Trading rules not satisfied ▲",IF(AND(LEFT(O12,6)="Same b",AND(LEFT(F12,5)<>LEFT(S12,5),I12>X12)),"Error - Trading rules not satisfied ▲",IF(AND(LEFT(O12,6)="Same h",F12<>S12),"Error - Trading rules not satisfied ▲",IF(AND(LEFT(O12,6)="Bespok",F12<>S12),"Error - Trading rules not satisfied ▲",IF(X12<I12,"Error Trading Down ▲",H12&" - "&W12))))))
Decoding the cell references and replacing them with names used in the XML version of this table, we get
=IF(~Ref="","",IF(AND(LEFT(~Baseline_Required_Action,6)="Same d",~Baseline_Distinctiveness _Score> ~Proposed_Distictiveness_Score),"Error - Trading rules not satisfied ▲",
IF(AND(LEFT(~Baseline_Required_Action,6)="Same b", AND(LEFT(Baseline_Habitat,5)<> LEFT(~Proposed_Habitat,5),~Baseline_Distinctiveness_Score > ~Proposed_Distictiveness_Score)),"Error - Trading rules not satisfied ▲",
IF(AND(LEFT(~Baseline_Required_Action,6)="Same h",~Baseline_Habitat <> Proposed_Habitat),"Error - Trading rules not satisfied ▲",
IF(AND(LEFT(~Baseline_Required_Action,6)="Bespok",~Baseline_Habitat <> Proposed_Habitat),"Error - Trading rules not satisfied ▲",
IF(~Proposed_Distinctiveness_Score < Baseline_Distinctiveness_Score ,"Error Trading Down ▲",concat(Baseline_Distinctiveness ," - " , ~Proposed_Distinctiveness)))))))
The use of LEFT() here fulfills two roles: to save testing the whole text of Required Action and to partially extract the Broad Habitat from the compound Broad Habitat - Habitat column used in the spreadsheet.
The required actions is taken from the Trading Notes of the baseline habitat which has the same set of values as Distinctiveness.
Bespoke compensation likely to be required
Same broad habitat or a higher distinctiveness habitat required
Same distinctiveness or better habitat required
Compensation Not Required
Even with the addition of meaningful names, the code is still difficult to understand. One reason is that this formula either reports a failure to satisfy trading rules or the change in distinctiveness if trading rules are satisfied. These purposes are really independent and it is clearer to break the column into two- one to show the change in distinctiveness, the other to validate the trading rules.
The second reason is that the tests are for the failure of a rule rather that its success. For example, the test for the Required_Action "Same broad habitat or a higher distinctiveness habitat required"
is tested by the condition
AND(LEFT(Baseline_Habitat,5) <> LEFT($self/Proposed_Habitat,5),$self/Baseline_Distinctiveness_Score > $self/Proposed_Distinctiveness_Score))
(Baseline_Broad_Habitat <> $self/Proposed_Broad_Habitat) and ($self/Baseline_Distinctiveness_Score >$self/Proposed_Distinctiveness_Score)
but its not immediately obvious that this is the negation of the rule which can be tested directly by the condition
(Baseline_Broad_Habitat= $self/Proposed_Broad_Habitat) OR($self/Baseline_Distinctiveness_Score < $self/Proposed_Distinctiveness_Score)
or better still since it is the Proposed Habitat which is being checked
(Proposed_Broad_Habitat= $self/Baseline_Broad_Habitat) OR ($self/Proposed_Distinctiveness_Score > $self/Baseline_Distinctiveness_Score)
which can now be easily seen to be a formal version of the rule itself.
If it is necessary to test for the failure of the rule, it is better to negate the rule :
Not (Proposed_Broad_Habitat= $self/Baseline_Broad_Habitat) OR ($self/Proposed_Distinctiveness_Score > $self/Baseline_Distinctiveness_Score)
The following XQuery expression is used as the compute rule in the model for validating the Trading rule:
if ($self/Baseline_Required_Action = "Bespoke compensation likely to be required")
then if ($self/Proposed_Habitat = $self/Baseline_Habitat) then "OK" else "Error - Trading rules not satisfied"
else if ($self/Baseline_Required_Action = "Same habitat required")
then if ($self/Proposed_Habitat = $self/Baseline_Habitat) then "OK" else "Error - Trading rules not satisfied"
else if ($self/Baseline_Required_Action = "Same distinctiveness or better habitat required")
then if ($self/Proposed_Distinctiveness_Score >=$self/Baseline_Distinctiveness_Score) then "OK" else "Error - Trading rules not satisfied"
else if ($self/Baseline_Required_Action = "Same broad habitat or a higher distinctiveness habitat required")
then if ($self/Proposed_Broad_Habitat =$self/Baseline_Broad_Habitat or $self/Proposed_Distinctiveness_Score > $self/Baseline_Distinctiveness_Score) then "OK" else "Error - Trading rules not satisfied"
else if ($self/Baseline_Required_Action = "Compensation Not Required") then "OK"
else concat("Error - Unknown Required action: ", $self/Baseline_Required_Action)
For traceability, it is good to be able to reference this calculation back to section 5.2 of the User Guide. However, because this document is provided only as a PDF and not an HTML document, it is not possible to provide a direct deep link into this part of the document.
Sadly, traceability doesn't seem to be possible for all computations in the spreadsheet. I am unable so far to trace the very next field which validates changes to Habitat_Condition.
Of course we have to trust (or laboriously check) that the same formula adjusted for position is used on all cells in the same column and in the Off-Site version of the sheet.
Although the XQuery language will be unfamiliar to many, this code is likely to be readable by most computer literate folk. This is partly due to the more natural English expressions used - 'let' , 'and' 'or' with '=' having its usual meaning, but perhaps comprehensibility is in the eye of the beholder.
On refection, rather than allowing a free choice of options and then validating that choice, it would better to restrict the available options in the first place. This also allows acceptable options to be seen. Validation is still needed however if data is imported from other sources.
