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
;)
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

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,343
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
10,853
Office Version
  1. 2019
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,453

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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,257
Messages
5,527,658
Members
409,780
Latest member
Sudheer121

This Week's Hot Topics

Top