#VALUE error appears before data calculates

Copper Contributor

Jan 15, 2021

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

ProTee
Copper Contributor
Jan 15, 2021
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!
- SergeiBaklan
  Diamond Contributor
  Jan 15, 2021
  ProTee , great to know you sorted this out, thank you for the update
ProTee
Copper Contributor
Jan 15, 2021
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
SergeiBaklan
Diamond Contributor
Jan 15, 2021
ProTee

And which exactly formulas in AZ3 and AZ21 where you have an error?
- ProTee
  Copper Contributor
  Jan 15, 2021
  SergeiBaklan
  
  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

Forum Discussion

#VALUE error appears before data calculates

Resources