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 |
Quote:
It's this: Code:
|
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") |
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. |
All times are GMT +1. The time now is 01:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com