#VALUE error appears before data calculates

[ProTee]

Created a great spreadsheet to measure distances and elevations for a local golf association.

Very last step is rid the blank spreadsheet of the #VALUE error.  Tried IFERROR w/o results.  Using 365 and the formula works as intended once data is calculated. Please review the .pdf attachment for better explanation.

Thanks in advance!

ProTee

[#2]

@ProTee 

Better if you provide small sample file just with formula in question, based on screenshot it's hard to say something concrete. Perhaps curly quotes, perhaps something else.

[#3]

@ProTee 

Excel formula error #VALUE

Several causes can lead to a #VALUE Error as it depends on the formula you used. An incorrect number or an invalid value listed as an argument can result in a #VALUE cell showing value Error.

Take an example: Enter = if (“a”, 1,0) this results in #value Error.

Method to fix #VALUE error in Excel

Preventing #VALUE Error and Correcting Your Excel Spreadsheet Below are a few options you can try:

Correct any data in the cell so that numeric data is always numeric

Use workarounds to exit #VALUE Error if the data is not the default

Create a SUM formula with the range of cells where the formula ignores incorrect data types.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

[#4]

Sergei, thank you for your reply!  I noticed I can remove all but one set of " " and the formula works perfectly fine yet the #VALUE still exists. (I've attached a screen shot for further clarity.)

Desired results: 

1)  No data --> No #VALUE or a blank cell

2)  Data entered --> Population of proper value from formula (e.g., +40, +30, +20 ... -30, -40)

G10's formula:    =IF(AN10=",",AN10)

K10's formula:    =IF(($G32-G10)>=35,+40,

                               IF(($G$32-G10)>=25,+30,

                               IF(($G$32-G10)>15,+20,

                               IF(($G$32G10>=+10,

                               IF(($G$32-G10)>-10,” “,

                               IF(($G$32-G10)<=-35,-40,

                               IF(($G$32-G10)<=-25,-30,

                               IF(($G$32-G10)<=-15,”-20,-10))))))))

AN10's formula:   =IFERROR(VLOOKUP(AM10,$AW$2:$AZ$700,4,0),"")

AZ3 is a Value

G32's formula:    =IF(AN32=",",AN32)

AN32's formula:  =IFERROR(VLOOKUP(AM32,$AW$2:$AZ$700,4,0),"")

AZ21 IS A VALUE

If I can supply further information, let me know.

Thanks, @ProTee

[#5]

@ProTee 

And which exactly formulas in AZ3 and AZ21 where you have an error?

[#6]

@ProTee  Sorry, the typed formula in my response ( IF(($G$32G10>=+10,) was incorrectly typed for this latest post.  The formula in the spreadsheet actually does have the minus (-) sign as follows:

IF(($G$32-G10>=+10,

Regards, @ProTee

[#7]

@Sergei Baklan 

I don't have any errors as far as I know ... everything calculates perfectly as designed once populated. 

Just want the #VALUE to go away and leave a clean/empty cell for the end user before they populate with data.  To them it looks like an error message before population when all the other cells are blank.

This spreadsheet is designed for 27 golf holes, the #VALUE appears 216 times in the entire spreadsheet:  27 holes * 8 Tees = 216 very noticeable #VALUE cells

[#8]

@ProTee 

Dear Community,

First, I want to thank those who spent their time attempting to find a solution to my dilemma!

As a "Newbie", trying to teach myself Excel the past 5+ months and approaching 70, I finally get to be a contributor instead of a seeker!

After much trial and error, I've come up with the solution to get rid of the #VALUE! populating the cells.  Here is the simple solution below (It's usually simple, isn't it?  But the road there is very difficult.  How else do we learn?)

Original formula:    =IF(($G32-G10)>=35,+40,

                                   IF(($G$32-G10)>=25,+30,

                                   IF(($G$32-G10)>15,+20,

                                   IF(($G$32G10>=+10,

                                   IF(($G$32-G10)>-10,” “,

                                   IF(($G$32-G10)<=-35,-40,

                                   IF(($G$32-G10)<=-25,-30,

                                   IF(($G$32-G10)<=-15,-20,-10))))))))

IMPROVED formula:   

     1st line edit         =IFERROR(IF(($G32-G10)>=35,+40,

                    Added =IFERROR(

   Last line edit           IF(($G$32-G10)<=-15,-20,-10)))))))),"")

                                                                                Added ,"")

Once again, a GREAT BIG THANKS to all of you!

[#9]

@ProTee , great to know you sorted this out, thank you for the update