Ask Your Question

calculate not working with decimal place

asked 2019-04-19 10:46:33 +0200

Hewtee gravatar image

If I try to use any formulae where one of the cells contains a number with a decimal point i.e. 100.50, I get a #VALUE error. Please help

edit retag flag offensive close merge delete


The information "contains a number with a decimal point i.e. 100.50" is worth nothing at all without telling the locale you are working in. In a german "DE" locale the "." (dot) is not a decimal delimiter, while in the US locale is. So please add at least your locale to the question or check your setting at Tools -> Options -> Language Settings -> Languages and the "Language" set in the "Format Cells" dialog for a formatted cell.

Opaque gravatar imageOpaque ( 2019-04-19 11:55:46 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-04-19 11:04:02 +0200

Sarum4n gravatar image

The cell containing 100.50 may be formatted as text, not a number. Right-click the cell, choose Format Cells (or select the cell and press Ctrl-1). Check that the formatting for the cell is of category Number.

edit flag offensive delete link more


Formatting a cell containing a text as a number doesn't change the text property of the content. The only solution would be to change the content to a number using the locale decimal delimiter.

Opaque gravatar imageOpaque ( 2019-04-19 12:28:46 +0200 )edit

Thanks for the quick reply. I checked settings under language and they were correct. However, I cannot find the set in the "Format Cells" dialog ?

Hewtee gravatar imageHewtee ( 2019-04-24 14:53:36 +0200 )edit

@Hewtee: Do not know to whom your comment is addressed to. The correct advice depends on the decimal delimiter your locale is set to. If it is "," (comma) then you need Find & Replace. If it is a "." (dot) your numbers are most probable text looking like numbers. So please tell us what locale your are using and what is the delimiter in that locale. PS: You cannot set the delimiter in der Format Cells dialog directly buit by changing the Lanuguage on left-most category). May be you can upload an anonymized sample file.

Opaque gravatar imageOpaque ( 2019-04-24 15:26:53 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-04-19 10:46:33 +0200

Seen: 28 times

Last updated: Apr 19