nOOb with Excel 2000 problem.

June 7, 2009 6:03 PM

I have been using a very basic formula in Excel since 1993 to track my checking account. This formula worked 3527 transactions without a problem before it started to return a #VALUE error. There is no obvious change or error in the formula.

The original formula, the last working instance of the formula and the formula in the first #VALUE were pasted into a .txt reader and compared. I successfully recreated the formula a few rows down. It worked for three rows and the #VALUE error returned. The Trace Error function does not show a problem when used on the first #VALUE cell. If the second, or any subsequent, #VALUE cell is checked, red dependent arrows lead down from the first row that has the #VALUE error. The blue precedent arrow remains in the first #VALUE row when any #VALUE cell (please bear with a nOOb here) is selected. The formula is =Balance as show in previous row-(atm withdraw+check amount+service charge)+(interest+deposit) I have pasted the problem part of the original sheet into a fresh, correctly formatted sheet and recreated the formula. The #VALUE problem occurs in the same row. I rounded all values down to the next whole dollar amount to remove the possibility of error being entered through values that contain decimal points.

Now for the weirdness. If the second bracketed part of the formula, +(interest+deposit), is deleted in only the first #VALUE row the correct sum replaces #VALUE. This row contained only a "check amount" value. When this corrected cell is dragged downward the full formula is restored and correct sums in each row register. The problem is gone but I have no idea what I did to correct it. Help!

posted by Raybun to computers & internet (13 comments total)

Mail me a copy of the spreadheet (address is in profile) and I'll have a look at it.
posted by flabdablet at 6:08 PM on June 7

You can email me too if you want.
posted by dfriedman at 6:23 PM on June 7

Could be an integer overflow, what number are you expecting in value? Greater than 32,767 ?
posted by mattoxic at 6:40 PM on June 7

Excel's calculation limits are far greater than 32,767. That shouldn't cause a #VALUE! error.
posted by dfriedman at 6:54 PM on June 7

Possibly column width, I've had this when the width was too narrow to display the value
posted by hungrysquirrels at 7:41 PM on June 7

dfriedman, if there is VBA performing the calculation, and the datatype is an integer then you will get an overflow error, the datatype needs to be a double.
posted by mattoxic at 7:42 PM on June 7

The OP refers to himself/herself as a "n00b".

I doubt someone so defined would be using VBA.
posted by dfriedman at 8:15 PM on June 7

Are you sure the other values—the atm withdraw, check amount, service charge, interest, & deposit—are all valid numbers for that first problem cell?
posted by fleacircus at 10:09 PM on June 7

Is there a typo somewhere? Maybe with a letter or other non-numeric symbol in it?
posted by Netzapper at 10:23 PM on June 7

Yeah check the column width/font size, sometimes that error displays if the value is simply too wide for the cell.
posted by EndsOfInvention at 3:28 AM on June 8

I am not using VB. Column width does not affect the error. A narrow column would give a ###### error. I did look for typos and formatting problems. The row with the first #VALUE error has only one dollar entry plus the reference to the previous balance.
posted by Raybun at 4:05 AM on June 8

For reference, I believe the issue here is that one of the cells was formatted as text and had some unseen character causing Excel to spit out an error.

My email to the OP:

Cell J5 is formatted as text and has some hidden character in it. To verify this use the following formula: =ISTEXT(J5). The value returned should be TRUE.

There are two solutions:

1) Format the cell as a number, or

2) copy a cell you know to be empty, and paste that empty cell's contents into cell J5.

Note that you can sum a series of cells formatted as text if there are numbers in those cells. If you try to sum a cell with a non-number in the cell, Excel won't know how to evaluate the text (absent a logic function assigning a numerical value to the text value) and so will return a #VALUE! error.

Let me know if this works.
posted by dfriedman at 8:43 AM on June 8

In the spreadsheet you sent me, cell J5 had a space character in it. That makes Excel treat it as a text value rather than an empty cell. If you include empty cells in numeric calculations, Excel will silently treat them as zeroes, but it won't do that for text - not even text that consists only of spaces.

I got rid of the #VALUE error just by clicking in J5, then clicking in the value bar, then pressing Backspace.
posted by flabdablet at 3:38 AM on June 9