98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Value#!
Value#!
Resolved · Low Priority · Version 2003
Stephanie has attended:
Excel Introduction course
Excel Intermediate course
Excel Advanced course
Value#!
When dealing with text in some spreadsheets the value#! has appeared even though there is no formula. I have had to change the cell formating is there a way to avoid this?
RE: Value#!
Hi Stephahie
Thank you for your post, and welcome to the forum;
Firstly ensure that your cells do not contain a space (space bar) Excel will return #VALUE! if you try to perform a calculation which includes cells which may have a space character; however if this is not the case; I have created a little example which might help:
Although some functions correctly evaluate cells and ignore text strings, if you add the cells by using arithmetic operators such as:
Addition (+)
Subtraction (-)
Multiplication (*)
Division (/)
...an error value may occur.
Instead of using an individual mathematical operator, use its equivalent worksheet function instead:
SUM (adds)
PRODUCT (multiplies)
QUOTIENT (divides)
For example, if you type the following information in cells A1:A7 of a worksheet
A1: 10
A2: text
A3: 20
A4: =A1+A2+A3 - incorrect
A5: =SUM(A1+A2+A3) - incorrect
A6: =SUM(A1,A2,A3) - correct
A7: =SUM(A1:A3) - correct
...the formulas in cells A4 and A5 return a #VALUE! error; however, cells A6 and A7 return the correct value of 30.
I hope that helps, regards Pete
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Changing Excel file and worksheet defaultsThe appearance of any new Excel files or any new worksheets that are inserted into a file are controlled by two template files, Book.xlt and Sheet.xlt. |