Help

Division Error in Formula

Topic Labels: Formulas
Solved
Jump to Solution
1545 8
cancel
Showing results for 
Search instead for 
Did you mean: 
John_Savidge
5 - Automation Enthusiast
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.

image

Here is my formula

image

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

Thanks in advance.
John

1 Solution

Accepted Solutions
augmented
10 - Mercury
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.

See Solution in Thread

8 Replies 8
augmented
10 - Mercury
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.

John_Savidge
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you so much, that worked!

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
5 - Automation Enthusiast
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()?

What is your current formula?

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?

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.

Thank you, will have a look at switch