# Re: Division Error in Formula

Solved
324 2
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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.

John

1 Solution

Accepted Solutions
10 - Mercury

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.

8 Replies 8
10 - Mercury

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.

5 - Automation Enthusiast

Thank you so much, that worked!

18 - Pluto

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}
5 - Automation Enthusiast

Thank you @kuovonne for the information.

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

Should I use value()?

18 - Pluto

5 - Automation Enthusiast

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?

18 - Pluto

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.

5 - Automation Enthusiast

Thank you, will have a look at switch