#ERROR! Returned when dividing two fields

[OTF_Admin]

Hi, I am trying to divide two fields using the following formula:

{Variable1}/{Variable2}

However I am returned a #ERROR!. I have checked and both fields are coded as Integer types and no value in “Variable2” is a zero.

Can you help me find out why I’m being returned this error?

[Justin_Barrett]

You said that the formatting of both variable fields is set to Integer, but you didn’t say what the field types were. Are they manual entry fields (number, currency, percent, etc), or calculated fields (formula, lookup, rollup, etc.)?

One way to force Airtable to see the contents of a field as a numeric value is to wrap the field name in VALUE():

VALUE({Variable 1}) / VALUE({Variable 2})

[Milos_Milosavlj]

Worked for me, definitely.

[Itai_El_al]

Justin_Barrett:

VALUE({Variable 1}) / VALUE({Variable 2})

My two fields are set to Number (as integer) and are manually inserted.

I’m trying to do the same, however I still get # ERROR.

What am I missing?

[Justin_Barrett]

In your case, you should not use the VALUE() wrapper function around your field names. All you need is this:

{Variable 1} / {Variable 2}

Per the formula documentation, VALUE() is designed to take a text string and convert it to a number. Your variable fields are already number types, so you’re feeding VALUE() the wrong type of data, which is why you get an error. Only when a field is not a number field would you wrap VALUE() around its name to extract the number for use in some mathematical calculation.

[Itai_El_al]

I see.

Well I’m trying to find the percentage of the amount of agreed monthly hours employees should have worked to hours they actually worked.

=SUM({Actual Hours}/{Monthly Hours})

I keep getting the number “1” for some reason.

I have both fields as numbers (integers).

[Justin_Barrett]

In that case you need to change the formatting of your formula field. It’s probably set to return an Integer by default, so it’s rounding the result.

On a different note, wrapping the division inside SUM() is unnecessary, unless you’re actually adding the result of that division to another number. All you need is:

{Actual Hours}/{Monthly Hours}

No leading = (that’s a spreadsheet thing), no SUM() wrapper.

[Itai_El_al]

Worked :slightly_smiling_face:

*Changed the formatting of my formula and removed =SUM

Thanks!

[Pan_Fuerza]

I’m having a similar issue, and thought adding VALUE() would solve it, but it hasn’t.
I’m trying to calculate unit cost by dividing two lookup fields named “Price” (formatted as currency) and “Size” (formatted as an integer). My attempts look like this so far, all coming up with an error result:

Price/Size
{price}/{size}
VALUE(price)/VALUE(size)
VALUE(price/size)

Any ideas?

[Justin_Barrett]

Welcome to the community, @Pan_Fuerza! :grinning_face_with_big_eyes: You’re going the right direction, but in your case VALUE() alone won’t do the trick. The VALUE() function requires a string as input, but both of your fields are lookups, and the output of a lookup is always an array, even when it’s only a single value in that array.

What you need to do first is convert each array to a string by concatenating it with an empty string, and then wrap VALUE() around each of those results. The third example you listed above is closest to this, so I’ll modify that:

VALUE(Price & "") / VALUE(Size & "")

You can now format that field as you wish for final display.