Avatar of hermesalpha
hermesalpha
Flag 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?
Microsoft OfficeMicrosoft ExcelSpreadsheets

Avatar of undefined
Last Comment
Shums Faruk

8/22/2022 - Mon
Shums Faruk

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

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
Shums Faruk

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Shums Faruk

Please find attached...
Data-Validation.xlsx
Your help has saved me hundreds of hours of internet surfing.
fblack61
hermesalpha

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).
Shums Faruk

Can you upload your sample workbook?
Shums Faruk

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
hermesalpha

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.
hermesalpha

ASKER
hermesalpha

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).
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Shums Faruk

Exactly, but your initial formula was with OR, I was trying to correct your formula. How would I know, you need different conditions :)
hermesalpha

ASKER
You are right, I saw that now, I only listed two values in my original question :(
Shums Faruk

So where I was wrong? :)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
hermesalpha

ASKER
Thanks, your OR solution was right for my original question.
Shums Faruk

You're Welcome hermesalpha! Glad I was able to help :)