hermesalpha
asked on
In Excel 2007, how should I correct my IF ERROR formula to avoid #VALUE! error
I have this formula in Q13:
=IF(OR($Q19="2: 1/4";"2: halv");0.225*1*0.25;0.225*1*0.5)
Then in Q19 I have a dropdown list with these values:

2: 1/4
2: halv
Note that the first value in this dropdown list is a  with several blank spaces before and after it. I suspect this might cause this #VALUE! error.
I get this #VALUE! error even after I have selected a valid option in the dropdown list (e.g. "2: 1/4").
Other things that might cause this could be formatting, such as centering of the contents in Q13 and Q19.
Or is there any wrong with my formula? How should I correct this so I get the calculation performed after having selected a valid option in the dropdown list?
=IF(OR($Q19="2: 1/4";"2: halv");0.225*1*0.25;0.225*
Then in Q19 I have a dropdown list with these values:

2: 1/4
2: halv
Note that the first value in this dropdown list is a  with several blank spaces before and after it. I suspect this might cause this #VALUE! error.
I get this #VALUE! error even after I have selected a valid option in the dropdown list (e.g. "2: 1/4").
Other things that might cause this could be formatting, such as centering of the contents in Q13 and Q19.
Or is there any wrong with my formula? How should I correct this so I get the calculation performed after having selected a valid option in the dropdown list?
ASKER
I entered your formula, and the #VALUE! error disappeared. But the calculation in Q13 should be performed when valid option has been selected in the dropdown list, now it does not. It should be displayed the result of the calculation in Q13 (which is 0.06 as I have formated Q13 as number with 2 decimals). Nothing is displayed in Q13 now.
ASKER CERTIFIED SOLUTION
membership
Create an account to see this answer
Signing up is free. No credit card required.
Please find attached...
DataValidation.xlsx
DataValidation.xlsx
ASKER
I get result calculated now when I add one more "Q19", but the result is wrong for the second option selected (correct only for the first option in the dropdown list selected). And if I select another value (not any of these two values, or the first option "  ", then I get wrong result (0.11).
Can you upload your sample workbook?
For every value you gonna add in Drop Down List, you need to add in your OR statement, suppose you added Q19 = 2: 3/4
=IF(OR($Q19="2: 1/4",$Q19="2: halv",$Q19="2: 3/4"),0.225*1*0.25,0.225*1*0.5)
ASKER
I know what is wrong now: The formula in fact says that if Q19=2 1/4 OR Q19=2: halv OR Q19=2: 3/4, then the first result, otherwise (if false), the last result.
ASKER
See Pass and Fail here: https://exceljet.net/excelfunctions/excelorfunction
ASKER
I found out the solution now:
=IF($Q$19="2: 1/4";0.225*1*0.25;IF($Q$19="2: halv";0.225*1*0.5;IF($Q$19="2: 3/4";0.225*1*0.75;IF($Q$19="2: hel";0.225*1*1;""))))
I need a nested IF function because there are more than two arguments (it can be more than only TRUE or FALSE).
=IF($Q$19="2: 1/4";0.225*1*0.25;IF($Q$19
I need a nested IF function because there are more than two arguments (it can be more than only TRUE or FALSE).
Exactly, but your initial formula was with OR, I was trying to correct your formula. How would I know, you need different conditions :)
ASKER
You are right, I saw that now, I only listed two values in my original question :(
So where I was wrong? :)
ASKER
Thanks, your OR solution was right for my original question.
You're Welcome hermesalpha! Glad I was able to help :)
Open in new window