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?
Retagged.
Math is the visual formula editor.
Calc is the calculation (spreadsheet) application which is the subject here.
The two are frequently confused.
Hello
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:
Hope that helps.
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.
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!
@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 - Absolutely. Good point.
I thought it would be implicit in my explanation, but of course it isn’t.