Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

value

ResolvedVersion 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?

Edited on Thu 21 Aug 2008, 08:18

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

Excel tip:

Shortcut fill a cell with contents from adjacent cells

Use Control + D to fill a cell with the data from an adjacent cell. This speeds up data entry and is a cool tip to share!


View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.