Circular dependency detected even while using IFERROR

Question

I have the following function in the cell B2:

ARRAYFORMULA(SUM(IFERROR(Purchases!B3:B, 0)))

And the following function in cell C2:

ARRAYFORMULA(SUM(IFERROR(Purchases!C3:C, 0)))

Basically, the first column (Column B) are prices in USD, and the second one (column A) are prices in LBP (Lebanese Pounds)

In the rest of the cells in Column B and C (i.e from B3/C3 till the end) i have the following:

    +------+----------+----------+
    |COL: A|  COL: B  |  COL: C  |
+---+------+----------+----------+
| 1 | text |   text   |   text   |
+---+------+----------+----------+
| 2 | text |   SUM    |   SUM    |
+---+------+----------+----------+ 
| 3 | text | =C3/1500 | =B3*1500 |
+---+------+----------+----------+
| 4 | text | =C4/1500 | =B3*1500 |
+---+------+----------+----------+
| 5 | text | =C5/1500 | =B3*1500 |
+---+------+----------+----------+
| 6 | text | =C6/1500 | =B6*1500 |
+---+------+----------+----------+

I will try to explain the spreadsheet:

Column A, is the description of the product purchased, which is just plain text.

Column B and C are the price of the product in USD and LBP respectively. Now, when I buy a product, sometimes I purchase it in USD, sometimes in LBP. So I want to add in its respective column, and when doing so, the other column will be automatically calculated and filled. The circular dependency in these cells are not a problem to me and totally understandable.

Issue

My problem are the cells B2 and C2, the first time I wrote the function, it worked like a charm. But after closing the document, the result of the function is a #REF Error. But if I edit the cell, and rewrite the same function, it returns the correct value. This has to be done, every time I reopen the document.

Am I doing something wrong? Or is this just a bug?

Is there another/better way to accomplish what I want? (Which is, price is available in both currencies, and if I input one currency, the second one is calculated automatically. And a cell with the total value of each currency)

P.S. I tried inputting the formulas this way too:

SUM(ARRAYFORMULA(IFERROR(Purchases!B3:B, 0)))

instead of

ARRAYFORMULA(SUM(IFERROR(Purchases!B3:B, 0)))

But I get the same issue.

Answer 1

The pure spreadsheet answer is to create columns D and E to display your information. An example row:

A3: text
B3: (enter USD if known)
C3: (enter LBP if known)
D3: = if( isblank(B3) ; C3/1500 ; B3 )
E3: = if( isblank(C3) ; B3*1500 ; C3 )

The other way you can solve this is with JavaScript, if you know it, using the onEdit trigger. In that case the JavaScript would detect the edit to B3 and automatically add the formula in C3, or vice versa.