Forum Discussion
#VALUE error appears before data calculates
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
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!
ProTee , great to know you sorted this out, thank you for the update
