Forum Discussion
#VALUE error appears before data calculates
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
8 Replies
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
And which exactly formulas in AZ3 and AZ21 where you have an error?
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
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)
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.
