Link to home
Create AccountLog in
Avatar of hermesalpha
hermesalphaFlag for Paraguay

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 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?
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Try below:
=IF(OR($Q19="2: 1/4",$Q19="2: halv"),0.225*1*0.25,0.225*1*0.5)

Open in new window

Avatar of hermesalpha

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
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Please find attached...
Data-Validation.xlsx
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)

Open in new window

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.
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).
Exactly, but your initial formula was with OR, I was trying to correct your formula. How would I know, you need different conditions :)
You are right, I saw that now, I only listed two values in my original question :(
So where I was wrong? :)
Thanks, your OR solution was right for my original question.
You're Welcome hermesalpha! Glad I was able to help :)