How to design autofill fields for exchange rate calculations?

Support
1

As far as I know, what I am trying to accomplish is impossible due to circular referencing. Please let me know if there is any workaround that doesn’t require the user to manually enter the result from one field to another.

Let’s say there are 3 fields on a sheet: two currency fields called USD Price and EUR Price, and a third field is an exchange rate field that automatically populates upon creation of the record.

The user either knows the USD price or the EUR price. If he knows the USD price, he types that into the USD Price field and the EUR field automatically calculates the EUR price based on the exchange rate in the exchange rate field. However, if the user enters the EUR price into the EUR Price field instead, I want the USD Price field to automatically calculate and populate. Automating either one of these calculations is super easy. But when you try to do both, this supposedly creates a circular reference that Ragic will not allow.

As far as I can see, because of the way Ragic calculates fields there should be no infinite loop. Entering a value manually into a field will not trigger a calculation of that field. And the field is ONLY re-calculated when a field it references is clicked into. Therefore, two fields that reference each other should only re-calculate one at a time when the other is changed manually.

Maybe those assumptions above are wrong, but any rate, it’s not allowed. But perhaps there is a workaround with an additional (hidden) field or two that can do some IF() or UPDATEIF() trickery? I am trying to avoid having separate fields for calculated results and manual entries.

2

Upon further testing, it seems my reasoning is incorrect. Even if I manually enter a number into the field, the calculation will overwrite it after the record is saved. If both fields were to reference each other then this would indeed cause an infinite loop. Therefore, there is probably no workaround that automatically updates these two price fields as described in the original post.

What would work is if there was a function to update a field only if it is already empty. Something like this:

UPDATEIFEMPTY(calculation)

A function like this would not create an infinite loop and would be helpful in many situations besides this one too. I will also suggest this in the Feature Request forum.

3

Hi,

Thanks for the case sharing, we would discuss with developers if we could make it supported.
If there’s any workaround can be helpful, we would also update in this thread.