Division Error in Formula

[John_Savidge]

Hi,

I am fairly new to Airtable and couldn’t find a solution to this here!

I have a formula that does a division calulation using the output from a formula field and a linked field value.

Here is my setup, I am getting #ERROR! in my calculation field.

Here is my formula

I have tried various combinations of using the VALUE() function but I can’t get it to work.

Thanks in advance.
John

[augmented · Accepted solution]

Hi John. While we wait for someone, who knows more than I, to give you the right answer, why don’t you try changing your numerator to

VALUE({Invoice Value}&'')

There is no space between the single quotes. This will make sure the VALUE function gets a string.

[John_Savidge]

Thank you so much, that worked!

[kuovonne]

Welcome to the Airtable community!

Thanks for the screen shots. There is a lot of information in them.

Your {Exchange Rate} field is a formula field that is returning a text string. That is why you needed to use VALUE() around the {Exchange Rage} in your {GBP Invoice Value} formula. I recommend revisiting the formula for {Exchange Rate} so that it returns a number instead of a text string.

Your {Invoice Value} field is a lookup field. Lookup fields can be the most difficult types of fields to use in formulas because it is difficult to predict their data types, Sometimes lookups act like numbers, sometimes they act like text strings, and sometimes they act like arrays (lists). When you use VALUE({Invoice Value}&'') you are taking a lookup value, converting it to a text string, and then converting that text string into a number. I recommend converting the field to a rollup instead, using a formula like MAX(values), which will consistently give you a number.

Once you have both the {Exchange Rate} and {Invoice Value} fields setup as numbers, then your {GBP Invoice Value} can be simply

{Invoice Value} / {Exchange Rate}

[John_Savidge]

Thank you @kuovonne for the information.

How can I make my exchange rate formula return as a number?

Should I use value()?

[kuovonne]

John_Savidge:

How can I make my exchange rate formula return as a number?

What is your current formula?

[John_Savidge]

IF({Currency (from Shipment)}=‘USD’,{USD (from Exchange Rates)},“”)

There will be more nested IF statements in future. Is it because I am returning blank string as the false output?

[kuovonne]

John_Savidge:

Is it because I am returning blank string as the false output?

Yes! Just leave off that third parameter and your formula will return a number.

You also might want to look into a SWITCH() function instead of nested IF()s.

[John_Savidge]

Thank you, will have a look at switch