Help

Division Error in Formula

Topic Labels: Formulas
Solved
Jump to Solution
88 8
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

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.

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}

Thank you @kuovonne for the information.

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

Should I use value()?

Labels