Use an "OR" around nested IF statements

[KeriM]

I have a pretty complicated IF statement, and I'm trying to put an "OR
around all my IF's and I'm getting a #Value. I tried to break it up fo
you guys...


Code
-------------------

=OR

(IF(AND(VALUE(H10)>=0,VALUE(H10)<=59, VALUE(H10-G10)>=0.06),"Y","N"),

(IF(AND(VALUE(H10)>=60,VALUE(H10)<=74, VALUE(H10-G10)>=0.05),"Y","N")),

(IF(AND(VALUE(H10)>=75,VALUE(H10)<=84, VALUE(H10-G10)>=0.04),"Y","N")),

(IF(AND(VALUE(H10)>=85,VALUE(H10)<=92, VALUE(H10-G10)>=0.03),"Y","N")),

(IF(AND(VALUE(H10)>=93,VALUE(H10)<=96, VALUE(H10-G10)>=0.02),"Y","N")),

(IF(AND(VALUE(H10)>=97,VALUE(H10)<=99, VALUE(H10-G10)>=0.01),"Y","N")))


-------------------


They work invidually and give me either a "Y" or "N", but it jus
doesn't like that OR around all of them. I need to test cell H1
against all these conditions to see where it falls. Any help i
appreciated! Thanks

 

[zvkmpw]

I have a pretty complicated IF statement, and I'm trying to put an "OR"

around all my IF's and I'm getting a #Value. I tried to break it up for
you guys...

=OR
(IF(AND(VALUE(H10)>=0,VALUE(H10)<=59, VALUE(H10-G10)>=0.06),"Y","N"),
(IF(AND(VALUE(H10)>=60,VALUE(H10)<=74, VALUE(H10-G10)>=0.05),"Y","N")),
(IF(AND(VALUE(H10)>=75,VALUE(H10)<=84, VALUE(H10-G10)>=0.04),"Y","N")),
(IF(AND(VALUE(H10)>=85,VALUE(H10)<=92, VALUE(H10-G10)>=0.03),"Y","N")),
(IF(AND(VALUE(H10)>=93,VALUE(H10)<=96, VALUE(H10-G10)>=0.02),"Y","N")),
(IF(AND(VALUE(H10)>=97,VALUE(H10)<=99, VALUE(H10-G10)>=0.01),"Y","N")))

They work invidually and give me either a "Y" or "N", but it just
doesn't like that OR around all of them.

OR(...) expects its arguments to be logical (TRUE/FALSE) values, not text ("Y"/"N") values.

So I'd suggest removing the embedded IF's to get something like this:
=IF(OR(AND(...), AND(...), AND(...), AND(...), AND(...), AND(...)),"Y","N")

 

[joeu2004]

I have a pretty complicated IF statement, and I'm trying
to put an "OR" around all my IF's and I'm getting a #Value.

You get a #VALUE error because you are effectively trying to calculate
=OR("Y","N",...), which is nonsense of course.

It is unclear what your intend logic is. If my guess below is incorrect, I
suggest that you explain you intended logic is English.

You might be try:

=IF(OR(AND(H10>= 0,H10<=59,H10-G10>=0.06),
AND(H10>=60,H10<=74,H10-G10>=0.05),
AND(H10>=75,H10<=84,H10-G10>=0.04),
AND(H10>=85,H10<=92,H10-G10>=0.03),
AND(H10>=93,H10<=96,H10-G10>=0.02),
AND(H10>=97,H10<=99,H10-G10>=0.01)),"Y","N")

But if you tell us more about the values in H10 and G10 (there range and
type) as well as the version of Excel that you use (or design for), we might
be able to simplify that.

PS: VALUE(H10) is needed only if H10 is numeric text instead of a number.
But even in that case, VALUE(H10-G10) is unnecessary.