Excel tip: How to fix #VALUE! errors

fix-excel-value-error
PeopleImages

The next instalment of David H Ringstrom’s trans-Atlantic series of Excel tips tackles the pesky problem of  #VALUE! alerts.

It’s frustrating when Excel shows #VALUE! in a cell rather than the expected result. Sometimes the reason is obvious, for example if you’ve tried to do a calculation that mixes text and numbers, but sometimes the culprit can be harder to pin down.

In the example below, the formula =C2/A2 returns #VALUE! because the formula attempted to divide the value 5000 in cell C2 by the word Apples in cell A2. The same mistake would occur if I tried to divide cell A2 by C2, or add or subtract one of those cells from the other. With that said, I could sum cells A2 through C2, as in =SUM(A2:C2), and the result would be... 

Please Login or Register to read the full article

The full article is available to registered AccountingWEB.co.uk members only. To read the rest of this article you’ll need to login or register. Registration is FREE and allows you to view all content, ask questions, comment and much more.

About David H Ringstrom

Replies

Please login or register to join the discussion.

avatar
12th Sep 2013 12:26

"Users sometimes erase values by tapping the spacebar"

Good grief! Bl**dy users!

I often despair at the appalling habits or limited training people get in Excel.

Mind you I've seen someone supposedly competent in a finance role create a formula which summed a cell on the same worksheet, a numeric variable typed directly into the formula and a link to a reference in another file entirely  and then send the file to someone who didn't have the referenced file. No wonder wrong results occur.

Thanks (0)