SOLVED

#VALUE!

Copper Contributor

Why has Excel, *all of a sudden,* started giving an error flag when doing simple mathematics that it had not done before.  By adding =if(H5="",I4,H5+I4).

If the cell contains an EMPTY cell, no spaces, nothing, it gives #VALUE!.  What happened from last week to this week that has created this agonizing blooper!

I really do not want to show a zillow 0's when the cell is empty.

I have also tried IFERROR with no change.

What has Excel done?

 

HELP!

 

 

16 Replies

@DennaB 

 

What has Excel done?

 

Probably nothing. It's generally premature to place the blame on Excel, especially when it comes to something as simple as adding two cells, even if you think that nothing has changed at your end.

 

My guess--especially since you mention Zillow in the process of your message-- is that something HAS changed at your end of things in those cells that are to be added. Especially if you are importing a CSV file from a source outside. There may be special characters embedded.....

 

Is it possible to post a copy of your spreadsheet in which this is happening? Not an image; the actual spreadsheet.

 

Absent that, describe a bit more fully the whole process prior to this error: in particular, where the data in cells H5 and I4 is coming from,

@mathetes 

 

I don't recall specifying "Zillow" in my prior message.

 

Let me update the spreadsheet as it's currently my personal checkbook and do not want to disclose that much of my personal details.  Then I'll upload a modified copy.

 

The cells are empty, I've already checked.  No extra spaces, I've already checked.

 

I do not know if it was Excel ... but do not know where else to point.  :)  Thought it might have been one of those automatic updates.

 

 

 

In this particular spreadsheet, I do not import any CSV file. It's strictly typing in details, or copy/pasting from a previous line.
P.S. Typo: Not zillow, it was suppose to be an exaggeration "zillion."
Hi @DennaB ,

#VALUE means the value is nothing, you have a wrong formula. Read everything slowly and you find the mistake in less than 5 minutes :D

Best regards,
Schnittlauch

"First, No system is safe. Second, Aim for the impossible. Third no Backup, no Mercy" - Schnittlauch

My answer helped you? Don't forget to leave a like. Also mark the answer as solved when your problem is solved. :)

@DennaB 

Let me update the spreadsheet as it's currently my personal checkbook and do not want to disclose that much of my personal details. Then I'll upload a modified copy.

In my own spreadsheet for tracking personal income and expenses, the data--checks written, credit card payments, etc--are all imported from the various banks (in CSV form usually). So I don't actually enter any. I add columns for Budget Category and Sub-Categories, then use Pivot Table to summarize by month and categories, all the income and expense lines. It's something I created on my own.

It's strictly typing in details, or copy/pasting from a previous line. The cells are empty, I've already checked. No extra spaces, I've already checked.

There is something in one or both of the cells, or possibly in the formatting of one or both of them, that causes Excel to give you the #VALUE message. Are the older lines in the check register still working, or have all of them gone wrong.

I do not know if it was Excel ... but do not know where else to point. Thought it might have been one of those automatic updates.

My only point was that you're not going to find the cause for that problem in Excel itself. That would mean that millions (zillions?) of people all over the world would suddenly be experiencing #VALUE errors in their spreadsheets. It would be on the front pages of the Wall Street Journal.  Which means, as @Schnittlauch has suggested, just go slowly through the formula (even try a different formula or two in the same cell, using the same cells) and the raw data. You'll find the cause a lot closer to home.

 

 

@mathetes 

 

Yes, of course I realize that Microsoft would be bombarded with complaints and all over the News.  :)

 

I'm very frustrated as it's one of those crazy things.   I would REALLY like to upload a version of the worksheet as I'm totally stumped.  To make it worse, being blessed with reaching the age of 77 this month, I'm a retired SAS/SQL programmer on UNIX & PC, using macros in Excel.  This is incredibly embarrassing.
attached is a modified copy with text removed and the numbers changed to be unreal.

AnImage_testExcel_Spreadsheet.jpg

@mathetes

Here is what it looks like with artificial numbers.  I cannot even figure how to upload my workbook.

                        500.00 
1-JanOnline    50.00                 550.00 
1-JanDeposit                    50.00                 600.00 
1-JanDeposit                    50.00                 650.00 
5-Jan2078     75                575.00 
5-JanOnline      75#VALUE! 
5-JanOnline     75#VALUE! 
5-JanOnline     75#VALUE! 
7-JanOnline     75#VALUE! 
7-JanOnline     75#VALUE! 
7-JanOnline     25#VALUE! 
7-JanISSUED     75#VALUE! 
8-Jan2075     75#VALUE! 
12-JanOnline      25#VALUE! 
12-JanOnline     25#VALUE! 
14-JanOnline     25#VALUE! 
17-JanOnline     75#VALUE! 
17-JanOnline     75#VALUE! 
17-JanOnline      #VALUE! 
21-JanOnline     75#VALUE! 
21-JanOnline    75.00 #VALUE! 
21-JanOnline     75#VALUE! 
21-JanScheduled    7575#VALUE! 
22-JanDeposit       #VALUE! 
26-JanOnline     75#VALUE! 
26-JanOnline                    50.00 #VALUE! 
27-JanOnline       #VALUE! 
29-JanDeposit    50.00 #VALUE! 
29-Jan      $             75.00 #VALUE! 
1-FebDeposit                    50.00 #VALUE! 
best response confirmed by allyreckerman (Microsoft)
Solution

@DennaB 

 

I can share in your blessing, but in my case three years ahead of you and still a few months away==I'll turn 80 in October.  I also share some of your experience as well--it wasn't my main career, but I did become something of a power user with Excel and its predecessors in the spreadsheet world (Lotus, Quattro), as well as learning SQL to write extensive queries from a mainframe database--and therefore can fully appreciate how frustrating it can be when, with all that experience, you can't put your finger on what's causing the #VALUE.

 

Unfortunately, an image isn't something that any of us can actually do much diagnostic work with.

 

Looking at the formula in the formula bar in the image, and the cell from which it appears to come (I748), I can tell:

  • the formula IS working in I748
  • and it's not in any of the subsequent rows--well, more precisely, it's working to deliver #VALUE

 

Which suggest something I'm sure you're well aware of, that the issue is most likely in whatever is in cells G749 and H749...and once introduced it doesn't self-correct since it's always in I749, I750, I751,... and on. Has the formula been changed in any way in any of the rows? I will offer the observation that the formula that's visible in this image is NOT the formula you included in your original post.

 

What happens if you place fresh numeric values in some of the cells in columns G, H and I down below where the #VALUE first appears? Down in the 760s?  

 

And since you made this image from a modified copy of the spreadsheet itself, is it now possible to post that actual--but modified and therefore not revealing sensitive personal data--spreadsheet?

Thank you SO much. Oh my. Lotus 1-2-3 and Quattro!!! I haven't heard those product names in YEARS. I loved Lotus 1-2-3 and Quattro was such a great and simple database.

I'll research the worksheet/spreadsheet and see if I can follow your suggestions.
At least I've learned to Knit. Hahaha.
P.S. Bet you were familar with Paradox, too. Another neat little database. :)

@DennaB 

 

To post an actual spreadsheet, you first go to the reply window

mathetes_0-1623170259482.png

 

then, down at the bottom, select "Open Full Text Editor" 

 

And at the bottom of the full text editor you'll see an area into which you can just Drag and Drop a copy of the full Excel file. It looks like this.

mathetes_1-1623170330344.png

And then click on "Post"

 

@mathetes 

 

Yes.  First, pardon the huge delay.  I had major water damage in my home, was displaced into an extended stay hotel, etc., etc..

 

Here is a mock Excel spreadsheet.  The error flags (the green rectangular flags) simply won't go away from anything I've tried. 

 

The only thing that I recall that has changed was taking in my laptop to the Geek Squad (Best Buy) for potential monitor issue but that resolved itself.  Meanwhile, I let them correct and remove duplicate malware that I had not realized was on my PC.   I've almost wondered if a new version of Excel needs to be installed.  

 

 

 

 

I got the #VALUE resolved but not the persistent green error flags.
I was able to use this message to return a formerly working formula back to working. I had to insert zeros in the cells that were empty.
This SS & formula has been working fine for 2 years with empty fields, but I have been doing quite a bit of moving things around in this SS when it started giving the #Value!.

What exactly is that about?
thanks for your help
Janice (also retired and long time computer user, not a programmer like you guys but worked closely with lots of them)

@Pennyloafer wrote: I had to insert zeros in the cells that were empty.
This SS & formula has been working fine for 2 years with empty fields, but I have been doing quite a bit of moving things around in this SS when it started giving the #Value!.

What exactly is that about?

 

I'm not sure what exactly your "exactly" is about: is it the appearance of #VALUE!?

 

And in the absence of the spreadsheet to examine, one can only speculate. My speculation is that those "empty fields" aren't really empty; they just appear empty, perhaps with spaces, perhaps with other special (but also invisible to the human eye) characters.

 

In any event, it's good to read that you got it working. And I'm so sorry to hear about the flooding and the temporary housing you had to endure. That's got to be very disorienting.

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@DennaB 

 

I can share in your blessing, but in my case three years ahead of you and still a few months away==I'll turn 80 in October.  I also share some of your experience as well--it wasn't my main career, but I did become something of a power user with Excel and its predecessors in the spreadsheet world (Lotus, Quattro), as well as learning SQL to write extensive queries from a mainframe database--and therefore can fully appreciate how frustrating it can be when, with all that experience, you can't put your finger on what's causing the #VALUE.

 

Unfortunately, an image isn't something that any of us can actually do much diagnostic work with.

 

Looking at the formula in the formula bar in the image, and the cell from which it appears to come (I748), I can tell:

  • the formula IS working in I748
  • and it's not in any of the subsequent rows--well, more precisely, it's working to deliver #VALUE

 

Which suggest something I'm sure you're well aware of, that the issue is most likely in whatever is in cells G749 and H749...and once introduced it doesn't self-correct since it's always in I749, I750, I751,... and on. Has the formula been changed in any way in any of the rows? I will offer the observation that the formula that's visible in this image is NOT the formula you included in your original post.

 

What happens if you place fresh numeric values in some of the cells in columns G, H and I down below where the #VALUE first appears? Down in the 760s?  

 

And since you made this image from a modified copy of the spreadsheet itself, is it now possible to post that actual--but modified and therefore not revealing sensitive personal data--spreadsheet?

View solution in original post