I have a nested IF formula acting on a cell far above it...when I copy the formula down or across, I get an #VALUE! error message, when the cell above is stark empty. The formula works when I put either a zero or a one into the cell upon which the IF formula is acting...

I can even remove the 1 or 0 from that reference cell once I have put the digit into the cell one time, and the IF formula below continues to work

I have tried formatting the reference cell as a number, but it continues to be a problem unless I fill the cell with a one or a zero, and then remove it.

I would use that as a solution, but the spreadsheet of reference cells is 100000 cells...too much work to do manually....help.

Edited by Moderator: All cap removed, Text from second thread added.

One added issue:

In simplest terms: How do I mark empty reference cells in such a way that a formula which acts upon them will see them as a number and not text, there in giving me that crummy #VALUE! error message...

So far, the only thing that works (formatting the cells in that range has failed) is to put a number in that reference cell, then take it out? NOt useful in a spreadsheet of 100000 reference cells, all being acted on by forumulas?

~~i HAVE A NESTED IF FORMULA ACTING ON A CELL FAR ABOVE IT...WHEN I COPY THE FORMULA DOWN OR ACROSS, I GET AN #VALUE! ERROR MESSAGE, WHEN THE CELL ABOVE IS STARK EMPTY~~

~~THE FORMULA WORKS WHEN I PUT EITHER A ZERO OR A ONE INTO THE CELL UPON WHICH THE IF FORMULA IS ACTING...~~

~~I CAN EVEN REMOVE THE 1 OR 0 FROM THAT REFERENCE CELL ONCE I HAVE PUT THE DIGIT INTO THE CELL ONE TIME, AND THE IF FORMULA BELOW CONTINUES TO WORK~~

~~I HAVE TRIED FORMATTING THE REFERENCE CELL AS A NUMBER, BUT IT CONTINUES TO BE A PROBLEM UNLESS I FILL THE CELL WITH A ONE OR A ZERO, AND THEN REMOVE IT~~

~~I WOULD USE THAT AS A SOLUTION, BUT THE SPREADSHEET OF REFERENCE CELLS IS 100000 CELLS...TOO MUCH WORK TO DO MANUALLY....HELP~~

edited by moderator: All Caps removed, text copied

Hi, It really would have been helpful if you included the formula that is causing the error, and also tell us what data is in the cells that you are referencing.

Also using all CAPS is considered to be shouting !

Please remember that we cannot see your spreadsheet, so we have no idea what you are trying to achieve, or how you are going about it, so some explanation would be in order.

Regards

First: Please refrain from using All Caps. All Caps is considered to be the Internet equivalent of shouting and is viewed as impolite.

Second: Imagine how much more help we could be if you included the formula that is giving you the #VALUE error.

Third: Please try to keep questions related to the same issue in a single thread. If members try to answer the same question in 2 different threads, things get really confusing, really fast.

I've added the text from your other thread to the OP in this thread and deleted the other thread.

Thanks!

DerbyDad03

Office Forum Moderator

Hi, Please .... when adding information about an existing post, stay with the same thread - use Reply.

As to #VALUE being crummy I have to disagree. #VALUE is an error message and if there is an error in your formula then you need to know about it.

You would end up with completely wrong results if errors were ignored, and you had no warning that an error had occurred.

If a cell is empty and you have a formula referring to it, then your formula should be designed to handle the empty cell, as well as other values that it might contain.

For example, if cell A1 can be empty or can contain a number, then use something like this:

=IF(A1="","Sorry - your cell is empty",IF(A1<10,"Smaller value","Larger value"))If error values are possible, then you can handle them logically with the ISERROR function: IF(ISERROR(A1),"",A1+A2)

If you are not sure what part of your formula is generating the error value you can use Formula auditing to evaluate the formula step by step. Another tool is to use trace error. I don't have Excel 2007 on this PC so I can't tell you where to find these tools - so just look in Excel help.

Regards

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History