Please help me in removing #value! error in my formula, thanks

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
IF(OR(R9<>"okay",OR(R8<>"okay",R10<>"okay")),"NO","Yes")

Fornula is as above, thanks
;)
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

AMAS

Active Member
Joined
Apr 11, 2010
Messages
472
I didn't get any errors...

And why do you have two ORs...

Try this:

Code:
=IF(OR(R9<>"okay",R8<>"okay",R10<>"okay"),"NO","Yes")

AMAS
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
i don't see an error or a reason for one ...
only the too complicated OR is bothering me, but it is not wrong
I completely agree with AMAS's post
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
I understand what you guys are saying but the cell example R9,R8,R10 has no value or has ### then it gives error to my if formula too..

so can you please take a look under microscope?:biggrin:
 

AMAS

Active Member
Joined
Apr 11, 2010
Messages
472

ADVERTISEMENT

OK, I put the formula into a new workbook and still no errors.

When everything is blank, the result of the formula is NO, which is what should appear.

AMAS
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
Try

=IF(COUNTIF(R8:R10,"Okay")<3,"NO","Yes")

Maybe you should look at correcting the errors in R8:R10 instead of trying to hide the problem.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456

ADVERTISEMENT

the cell example R9,R8,R10 has no value or has ### then it gives error to my if formula too..

Hi Pedie,
jasonb75 is right in looking at the formulas returning errors first.
(What, if any, formulas do you have in R8:R10?)

just for fun you could take that CountIf formula a bit further to tell you how many of them have a problem. ie.
Code:
=IF(COUNTIF(R8:R10,"Okay")=3,"Yes","Problem with " & 3-COUNTIF(R8:R10,"Okay")&" precedent cell(s).")

But still, the original errors are what to take care of first if possible.
 

AMAS

Active Member
Joined
Apr 11, 2010
Messages
472
OK, now I understand what you meant by ###... yes a formula referencing a cell with an error will return an error.

HalfAce, I like your debugging extension to jasonb75's formula. I will have to keep this in mind if I fall into a similar problem.

AMAS
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,303
Messages
5,836,526
Members
430,437
Latest member
Emilycr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top