[Solved] Cell won't accept my formula, says #value!

[handyman2014]

I recently got a new computer with Open Office installed on it. v4.0.1 I transferred my excel spreadsheets to open office. Some of the formulas worked and some of them show #value! in the cell instead of the formula. I have tried over and over to enter the formula into these cells and they just keep saying #value!. I even made some new spreadsheets of the same info. such as payroll and it won't accept my formulas either. Same result when I enter them into a cell. Example of formula: =sum(C6*1.5*D6)
Any help with why I keep getting the "#value!" deal in the cell instead of my formula would be greatly appreciated.
Thank you.

[FJCC]

It is hard to be sure without seeing the data but a likely cause is that some of your "numbers" are actually text. Excel is much more likely to automatically convert text to numbers. Try selecting View -> Value Highlighting. Numbers will be blue, text will be black and formula results will be green. If that doesn't solve the problem for you, please upload a small example file using the Upload Attachment tab just under the box where you type a response.

[Villeroy]

=sum(C6*1.5*D6) multiplies C6 by 1.5 and by D6 before it sums up the single result of the multiplications. The sum of a single number is the same as that number so we can simplify it without loss to:
=C6*1.5*D6
When you turn on menu:View>"Value Highlighting" [Ctrl+F8] all number should appear in blue font. If either one of C6 or D6 shows black font, the multiplication fails.

Other ways to test if a cell has a number of a text:
=ISTEXT(C6) => TRUE if C6 is text, FALSE otherwise
=ISNUMBER(C6) => TRUE if C6 is a number, FALSE otherwise

Select a text cell that shold be a number, call menu:Format>"Default Formatting" [Ctrl+M] and re-enter the value.

[handyman2014]

It is hard to be sure without seeing the data but a likely cause is that some of your "numbers" are actually text. Excel is much more likely to automatically convert text to numbers. Try selecting View -> Value Highlighting. Numbers will be blue, text will be black and formula results will be green. If that doesn't solve the problem for you, please upload a small example file using the Upload Attachment tab just under the box where you type a response.

OK, I have uploaded an example of the data sheet with a note on it. Also, I did change the sprdsht. to value highlighting so I do understand how that works, but that is as far as I can see.
Can you help me with why some formulas work and some don't and what do I do to correct this?

Thank you, handy

[Villeroy]

You see the black 15.00 in C9? F9 refers to that text, returns #VALUE and the other errors refer to F9. Enter 15 into C9 and everything should be fine. I don't know how the text got inot C9. May be some input error.

[handyman2014]

You see the black 15.00 in C9? F9 refers to that text, returns #VALUE and the other errors refer to F9. Enter 15 into C9 and everything should be fine. I don't know how the text got inot C9. May be some input error.

Thank you so much.

I went to cell C9 and there was like an apostrophe before the 15.00 ('15.00).
The little mark was so tiny I didn't even notice it. And it wasn't showing unless I clicked on that cell.

Anyway, that removed the #value! error and all is fine now. I hope when I put the formulas into my 2014
spreadsheet, that all will be fine also. Because they were doing the same thing. Some formulas worked and
some didn't. So I have been doing it all by hand, without formulas.
Again, thank you.