# Iterative references in LibreOffice Calc

An as yet unanswered Q from Stack Overflow [SO] enquired:

how to stop looping this formula

where the formulae were:

in B9 =16,50

in B10 =B9-B11

in B11 =B10/2

without changing text in cell.

(The above after changing `*`

to `/`

based on a comment. It can also be deduced from that comment that `16,5`

is a value ("sixteen and a half") and that the formulae should return `11`

in B10 and `5,5`

or `5.5`

in B11.)

The effect is to split the B9 value into 2/3rds (B10) and1/3rd (B11) but this was an academic exercise with the artificial constraint that:

My lecturer want to 3 option... He didn't let me to change formulas in cell, he want explanation error and "magic solve".

So, how to achieve the desired split without changing the formulae?

## 1 answer

Since the formulae have circular references (Err:522) the spreadsheet setting needs to be changed to enable iteration (Tools > Options… > LibreOffice Calc > Calculation, tick Iterations and choose a sensible combination of `Steps:`

and `Minimum change:`

, OK). For current purposes I suggest `100`

for Steps; and `0.01`

for Minimum change:.

The iteration is the looping required and `Steps:`

and `Minimum change:`

limit the number of iterations.

The user's difficulty might have been because of the formula error (multiply should have been divide) since, as mentioned by SO user @tohuwawohu:

it won't work for factors >=1 multiplied with B10. I can't tell why

I don't know why either, but do remember a bug.

## 0 comments