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
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.
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.
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.
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: 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.