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*
Then in Q19 I have a drop-down list with these values:
-
2: 1/4
2: halv
Note that the first value in this drop-down 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 drop-down 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 drop-down list?
=IF(OR($Q19="2: 1/4";"2: halv");0.225*1*0.25;0.225*
Then in Q19 I have a drop-down list with these values:
-
2: 1/4
2: halv
Note that the first value in this drop-down 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 drop-down 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 drop-down list?
Microsoft OfficeMicrosoft ExcelSpreadsheets
Last Comment
Shums Faruk
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 drop-down 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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Please find attached...
Data-Validation.xlsx
Data-Validation.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 drop-down 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/excel-functions/excel-or-function
ASKER
I found out the solution now:
=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).
=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