Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Use an "OR" around nested IF statements
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...
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"))) |
#2
|
|||
|
|||
Quote:
It's this: Code:
=IF(OR( AND(VALUE(H10)=0,VALUE(H10)<=59, VALUE(H10-G10)=0.06), AND(VALUE(H10)=60,VALUE(H10)<=74, VALUE(H10-G10)=0.05), AND(VALUE(H10)=75,VALUE(H10)<=84, VALUE(H10-G10)=0.04), AND(VALUE(H10)=85,VALUE(H10)<=92, VALUE(H10-G10)=0.03), AND(VALUE(H10)=93,VALUE(H10)<=96, VALUE(H10-G10)=0.02), AND(VALUE(H10)=97,VALUE(H10)<=99, VALUE(H10-G10)=0.01)),"Y","N") |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use an "OR" around nested IF statements
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") |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use an "OR" around nested IF statements
"KeriM" wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
nested if statements using "AND" as well as "OR" | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Nested "IF" With "And"and "OR" functions | Excel Worksheet Functions | |||
Need more than 7 nested "IF" statements in Excel" | Excel Discussion (Misc queries) | |||
nested "with .../end with" statements | Excel Programming |