Ask Your Question

why is my simple formula: =e480-c481 d481 giving error msg:

asked 2019-10-13 19:23:42 +0200 gravatar image

updated 2019-10-13 21:33:00 +0200

keme gravatar image

why is my simple formula: =e480-c481+d481 giving error msg: #VALUE! in libreoffice for location e481 but similiar works for locations: e10 to e480?

edit retag flag offensive close merge delete



Math is the visual formula editor.

Calc is the calculation (spreadsheet) application which is the subject here.

The two are frequently confused.

keme gravatar imagekeme ( 2019-10-13 21:40:24 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-10-13 19:38:42 +0200

Opaque gravatar image


most probably one or more of the values in cells e480, c481 and d481 is not a number but text. See also Error Codes in LibreOffice which states for Error 519:

image description

Hope that helps.

edit flag offensive delete link more



Elaborating on Opaque's explanation: If the text contains an unambiguous number, and only that number, Calc will interprete the text as numerical value for the formula you showed.

Note that it will not convert such numerical texts for aggregation functions such as SUM() and AVERAGE(). This may lead to surprising results sometimes.

Common cases when the #VALUE! error occurs because Calc is unable to convert to number

  • Wrong number format according to the locale ("international settings", see below)
    • Foreign currency symbol is typed in with amount.
    • Wrong thousands separator/decimal separator used.
  • Unit typed in with magnitude
  • Cell is "blanked" by inserting a space.

Calc will usually handle currency symbol and thousands separator well, but they must be used correctly according to settings in Tools - Options - Language settings - Local.

keme gravatar imagekeme ( 2019-10-13 20:44:22 +0200 )edit

retyping wanted value in d column didn't work. But typing new value in c column, then typing new value in d column worked! Then was able to put wanted value in d column? Thank you! gravatar ( 2019-10-13 22:33:32 +0200 )edit

@keme - though all of your comment about conversion is absolutely correct, I'd state Never design your sheet in a way relying on any type conversion

Opaque gravatar imageOpaque ( 2019-10-13 23:10:54 +0200 )edit

@Opaque - Absolutely. Good point.

I thought it would be implicit in my explanation, but of course it isn't.

keme gravatar imagekeme ( 2019-10-14 10:04:02 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-10-13 19:23:42 +0200

Seen: 51 times

Last updated: Oct 13 '19