If(AND formula

  • Thread starter Thread starter Wildwood
  • Start date Start date
W

Wildwood

Please help......this formula is returning a Value error.......what is wrong?

=IF(I8="1","0"),IF(AND(F3="1",I8=4),"48%",IF(AND(F3="1",I8=3),"53%",IF(AND(F3="1",I8=2),"60%",IF(AND(F3="2",I8=4),"65%",IF(AND(F3="2",I8=3),"70%",IF(AND(F3="2",I8=2),"70%"))))))
 
The parenthesis after "0" is out of place. Also, you are mixing numbers and
text, which is certain to cause you problems. Try the following:
=IF(I8=1,0,IF(AND(F3=1,I8=4),48%,IF(AND(F3=1,I8=3),53%,IF(AND(F3=1,I8=2),60%,IF(AND(F3=2,I8=4),65%,IF(AND(F3=2,I8=3),70%,IF(AND(F3=2,I8=2),70%)))))))

You don't say what happens if none of the above are true, which I left for
you to fix.

Regards,
Fred
 
try this:
=IF(AND(F3=1,I8=4),"48%",IF(AND(F3=1,I8=3),"53%",IF(AND(F3=1,I8=2),"60",IF(AND(F3=2,I8=4),"65%",IF(AND(F3=2,I8=3),"70",IF(AND(F3=2,I8=2),"70",0))))))

Hope it helps
 
Wildwood said:
this formula is returning a Value error.......what is wrong?
=IF(I8="1","0"),
IF(AND(F3="1",I8=4),"48%",
IF(AND(F3="1",I8=3),"53%",
IF(AND(F3="1",I8=2),"60%",
IF(AND(F3="2",I8=4),"65%",
IF(AND(F3="2",I8=3),"70%",
IF(AND(F3="2",I8=2),"70%"))))))
Click to expand...

The fundamental flaw is the first right-parenthesis. You should write:

=IF(I8="1","0", [...etc...]

and add a closing right-parenthesis at the end.

But even after correcting that, your formula is undesirable, if not
incorrect, because you put quotes around numbers. Also, if you are using
Excel 2003, you have reached the maximum function nesting level, which would
make it difficult to add conditions later.

Moreover, you do not handle the cases when F3 is neither 1 or 2 and when I8
is not one of 1 through 4. That might be okay, if you are sure those error
conditions never arise.

One of the following might be a better way to go.

1. If you are sure that F3 is 1 or 2 and I8 is one of 1 through 4:

=IF(F3=1, CHOOSE(I8,0,60%,53%,48%),
CHOOSE(I8,0,70%,70%,65%))

2. If you want to handle unexpected F3 and I8:

=IF(AND(F3=1,1<=I8,I8<=4), CHOOSE(I8,0,60%,53%,48%),
IF(AND(F3=2,1<=I8,I8<=4), CHOOSE(I8,0,70%,70%,65%), 0))

That results in zero for unexpected F3 and I8. You might prefer a different
result. If you want the cell to appear blank, replace 0 with the null string
("", two double-quotes).

For Excel 2007 and later, you could write:

=IFERROR(IF(F3=1, CHOOSE(I8,0,60%,53%,48%),
IF(F3=2, CHOOSE(I8,0,70%,70%,65%), 0), 0)

Again, the last two zeros represent results for unexpected conditions.


----- original message -----
 
Errata (typo)....
For Excel 2007 and later, you could write:
=IFERROR(IF(F3=1, CHOOSE(I8,0,60%,53%,48%),
IF(F3=2, CHOOSE(I8,0,70%,70%,65%), 0), 0)
Click to expand...

That should be:

=IFERROR(IF(F3=1, CHOOSE(I8,0,60%,53%,48%),
IF(F3=2, CHOOSE(I8,0,70%,70%,65%), 0)), 0)


----- original message -----

Joe User said:
Wildwood said:
this formula is returning a Value error.......what is wrong?
=IF(I8="1","0"),
IF(AND(F3="1",I8=4),"48%",
IF(AND(F3="1",I8=3),"53%",
IF(AND(F3="1",I8=2),"60%",
IF(AND(F3="2",I8=4),"65%",
IF(AND(F3="2",I8=3),"70%",
IF(AND(F3="2",I8=2),"70%"))))))
Click to expand...

The fundamental flaw is the first right-parenthesis. You should write:

=IF(I8="1","0", [...etc...]

and add a closing right-parenthesis at the end.

But even after correcting that, your formula is undesirable, if not
incorrect, because you put quotes around numbers. Also, if you are using
Excel 2003, you have reached the maximum function nesting level, which would
make it difficult to add conditions later.

Moreover, you do not handle the cases when F3 is neither 1 or 2 and when I8
is not one of 1 through 4. That might be okay, if you are sure those error
conditions never arise.

One of the following might be a better way to go.

1. If you are sure that F3 is 1 or 2 and I8 is one of 1 through 4:

=IF(F3=1, CHOOSE(I8,0,60%,53%,48%),
CHOOSE(I8,0,70%,70%,65%))

2. If you want to handle unexpected F3 and I8:

=IF(AND(F3=1,1<=I8,I8<=4), CHOOSE(I8,0,60%,53%,48%),
IF(AND(F3=2,1<=I8,I8<=4), CHOOSE(I8,0,70%,70%,65%), 0))

That results in zero for unexpected F3 and I8. You might prefer a different
result. If you want the cell to appear blank, replace 0 with the null string
("", two double-quotes).

For Excel 2007 and later, you could write:

=IFERROR(IF(F3=1, CHOOSE(I8,0,60%,53%,48%),
IF(F3=2, CHOOSE(I8,0,70%,70%,65%), 0), 0)

Again, the last two zeros represent results for unexpected conditions.


----- original message -----

Wildwood said:
Please help......this formula is returning a Value error.......what is wrong?

=IF(I8="1","0"),IF(AND(F3="1",I8=4),"48%",IF(AND(F3="1",I8=3),"53%",IF(AND(F3="1",I8=2),"60%",IF(AND(F3="2",I8=4),"65%",IF(AND(F3="2",I8=3),"70%",IF(AND(F3="2",I8=2),"70%"))))))
Click to expand...
Click to expand...
 
I insert your formula and it returns a zero now.......here is what it looks
like......any other suggestions??? Right now, on the worksheet F3=1 and
I8=4.....so by definition, this cell should be displaying 48% but it is
not......

=IF($I$8=1,0,IF(AND($F$3=1,$I$8=4),48%,IF(AND($F$3=1,$I$8=3),53%,IF(AND($F$3=1,$I$8=2),60%,IF(AND($F$3=2,$I$8=4),65%,IF(AND($F$3=2,$I$8=3),70%,IF(AND($F$3=2,$I$8=2),70%,0)))))))
 
I'm using Excel 2007 - - but neither of your suggestions worked......

Joe User said:
Errata (typo)....
For Excel 2007 and later, you could write:
=IFERROR(IF(F3=1, CHOOSE(I8,0,60%,53%,48%),
IF(F3=2, CHOOSE(I8,0,70%,70%,65%), 0), 0)
Click to expand...

That should be:

=IFERROR(IF(F3=1, CHOOSE(I8,0,60%,53%,48%),
IF(F3=2, CHOOSE(I8,0,70%,70%,65%), 0)), 0)


----- original message -----

Joe User said:
Wildwood said:
this formula is returning a Value error.......what is wrong?
=IF(I8="1","0"),
IF(AND(F3="1",I8=4),"48%",
IF(AND(F3="1",I8=3),"53%",
IF(AND(F3="1",I8=2),"60%",
IF(AND(F3="2",I8=4),"65%",
IF(AND(F3="2",I8=3),"70%",
IF(AND(F3="2",I8=2),"70%"))))))
Click to expand...

The fundamental flaw is the first right-parenthesis. You should write:

=IF(I8="1","0", [...etc...]

and add a closing right-parenthesis at the end.

But even after correcting that, your formula is undesirable, if not
incorrect, because you put quotes around numbers. Also, if you are using
Excel 2003, you have reached the maximum function nesting level, which would
make it difficult to add conditions later.

Moreover, you do not handle the cases when F3 is neither 1 or 2 and when I8
is not one of 1 through 4. That might be okay, if you are sure those error
conditions never arise.

One of the following might be a better way to go.

1. If you are sure that F3 is 1 or 2 and I8 is one of 1 through 4:

=IF(F3=1, CHOOSE(I8,0,60%,53%,48%),
CHOOSE(I8,0,70%,70%,65%))

2. If you want to handle unexpected F3 and I8:

=IF(AND(F3=1,1<=I8,I8<=4), CHOOSE(I8,0,60%,53%,48%),
IF(AND(F3=2,1<=I8,I8<=4), CHOOSE(I8,0,70%,70%,65%), 0))

That results in zero for unexpected F3 and I8. You might prefer a different
result. If you want the cell to appear blank, replace 0 with the null string
("", two double-quotes).

For Excel 2007 and later, you could write:

=IFERROR(IF(F3=1, CHOOSE(I8,0,60%,53%,48%),
IF(F3=2, CHOOSE(I8,0,70%,70%,65%), 0), 0)

Again, the last two zeros represent results for unexpected conditions.


----- original message -----

Wildwood said:
Please help......this formula is returning a Value error.......what is wrong?

=IF(I8="1","0"),IF(AND(F3="1",I8=4),"48%",IF(AND(F3="1",I8=3),"53%",IF(AND(F3="1",I8=2),"60%",IF(AND(F3="2",I8=4),"65%",IF(AND(F3="2",I8=3),"70%",IF(AND(F3="2",I8=2),"70%"))))))
Click to expand...
Click to expand...
Click to expand...
 
Wildwood said:
neither of your suggestions worked
Click to expand...

I can only guess that F3 and I8 contain __text__ that appears as numbers,
not real numbers, just as you done in your original IF expression.

To verify, replace F3 and I8 in my suggestions with N(F3) and N(I8) in all
places. My guess is: the formula will now work.

But that means there is a broader problem that you need to address.

Ideally, all the counts of all cells that are currently text, but contain
numeric strings should be changed to real numbers. But that is hard to guide
you at arm's length like this.

If you would like to pursue that, we could discuss here, or you could send
me email at joue2004 "at" hotmail.com.

Alternatively, you could continue the mistake (IMHO) by putting quotes
around all numbers in my suggestions. For example:

=IFERROR(IF(F3="1", CHOOSE(N(I8),"0","60%","53%","48%"),
IF(F3="2", CHOOSE(N(I8),"0","70%","70%","65%"), "0")), "0")


----- original message -----

Wildwood said:
I'm using Excel 2007 - - but neither of your suggestions worked......

Joe User said:
Errata (typo)....
For Excel 2007 and later, you could write:
=IFERROR(IF(F3=1, CHOOSE(I8,0,60%,53%,48%),
IF(F3=2, CHOOSE(I8,0,70%,70%,65%), 0), 0)
Click to expand...

That should be:

=IFERROR(IF(F3=1, CHOOSE(I8,0,60%,53%,48%),
IF(F3=2, CHOOSE(I8,0,70%,70%,65%), 0)), 0)


----- original message -----

Joe User said:
:
this formula is returning a Value error.......what is wrong?
=IF(I8="1","0"),
IF(AND(F3="1",I8=4),"48%",
IF(AND(F3="1",I8=3),"53%",
IF(AND(F3="1",I8=2),"60%",
IF(AND(F3="2",I8=4),"65%",
IF(AND(F3="2",I8=3),"70%",
IF(AND(F3="2",I8=2),"70%"))))))

The fundamental flaw is the first right-parenthesis. You should write:

=IF(I8="1","0", [...etc...]

and add a closing right-parenthesis at the end.

But even after correcting that, your formula is undesirable, if not
incorrect, because you put quotes around numbers. Also, if you are using
Excel 2003, you have reached the maximum function nesting level, which would
make it difficult to add conditions later.

Moreover, you do not handle the cases when F3 is neither 1 or 2 and when I8
is not one of 1 through 4. That might be okay, if you are sure those error
conditions never arise.

One of the following might be a better way to go.

1. If you are sure that F3 is 1 or 2 and I8 is one of 1 through 4:

=IF(F3=1, CHOOSE(I8,0,60%,53%,48%),
CHOOSE(I8,0,70%,70%,65%))

2. If you want to handle unexpected F3 and I8:

=IF(AND(F3=1,1<=I8,I8<=4), CHOOSE(I8,0,60%,53%,48%),
IF(AND(F3=2,1<=I8,I8<=4), CHOOSE(I8,0,70%,70%,65%), 0))

That results in zero for unexpected F3 and I8. You might prefer a different
result. If you want the cell to appear blank, replace 0 with the null string
("", two double-quotes).

For Excel 2007 and later, you could write:

=IFERROR(IF(F3=1, CHOOSE(I8,0,60%,53%,48%),
IF(F3=2, CHOOSE(I8,0,70%,70%,65%), 0), 0)

Again, the last two zeros represent results for unexpected conditions.


----- original message -----

:
Please help......this formula is returning a Value error.......what is wrong?

=IF(I8="1","0"),IF(AND(F3="1",I8=4),"48%",IF(AND(F3="1",I8=3),"53%",IF(AND(F3="1",I8=2),"60%",IF(AND(F3="2",I8=4),"65%",IF(AND(F3="2",I8=3),"70%",IF(AND(F3="2",I8=2),"70%"))))))
Click to expand...
Click to expand...
Click to expand...
 
First thing to look for is the format for this cell. If it's formatted as a
number with zero decimal places, you would get a 0 result displayed. Make
sure it's formatted as a percentage.
Next problem could be text versus numbers. Does F3 equal 1 or "1"? Same with
I8 -- is it 4 or "4"? How was the data entered, or what is the formula for
these two cells.

Regards,
Fred
 
Wildwood said:
neither of your suggestions worked.
Click to expand...

PS: It would helpful if you defined "not worked".

I neglected to note that the cells with any of my formulas should be
formatted as Percentage. That is true for any formula that returns true
percentages.


----- original message -----

Wildwood said:
I'm using Excel 2007 - - but neither of your suggestions worked......

Joe User said:
Errata (typo)....
For Excel 2007 and later, you could write:
=IFERROR(IF(F3=1, CHOOSE(I8,0,60%,53%,48%),
IF(F3=2, CHOOSE(I8,0,70%,70%,65%), 0), 0)
Click to expand...

That should be:

=IFERROR(IF(F3=1, CHOOSE(I8,0,60%,53%,48%),
IF(F3=2, CHOOSE(I8,0,70%,70%,65%), 0)), 0)


----- original message -----

Joe User said:
:
this formula is returning a Value error.......what is wrong?
=IF(I8="1","0"),
IF(AND(F3="1",I8=4),"48%",
IF(AND(F3="1",I8=3),"53%",
IF(AND(F3="1",I8=2),"60%",
IF(AND(F3="2",I8=4),"65%",
IF(AND(F3="2",I8=3),"70%",
IF(AND(F3="2",I8=2),"70%"))))))

The fundamental flaw is the first right-parenthesis. You should write:

=IF(I8="1","0", [...etc...]

and add a closing right-parenthesis at the end.

But even after correcting that, your formula is undesirable, if not
incorrect, because you put quotes around numbers. Also, if you are using
Excel 2003, you have reached the maximum function nesting level, which would
make it difficult to add conditions later.

Moreover, you do not handle the cases when F3 is neither 1 or 2 and when I8
is not one of 1 through 4. That might be okay, if you are sure those error
conditions never arise.

One of the following might be a better way to go.

1. If you are sure that F3 is 1 or 2 and I8 is one of 1 through 4:

=IF(F3=1, CHOOSE(I8,0,60%,53%,48%),
CHOOSE(I8,0,70%,70%,65%))

2. If you want to handle unexpected F3 and I8:

=IF(AND(F3=1,1<=I8,I8<=4), CHOOSE(I8,0,60%,53%,48%),
IF(AND(F3=2,1<=I8,I8<=4), CHOOSE(I8,0,70%,70%,65%), 0))

That results in zero for unexpected F3 and I8. You might prefer a different
result. If you want the cell to appear blank, replace 0 with the null string
("", two double-quotes).

For Excel 2007 and later, you could write:

=IFERROR(IF(F3=1, CHOOSE(I8,0,60%,53%,48%),
IF(F3=2, CHOOSE(I8,0,70%,70%,65%), 0), 0)

Again, the last two zeros represent results for unexpected conditions.


----- original message -----

:
Please help......this formula is returning a Value error.......what is wrong?

=IF(I8="1","0"),IF(AND(F3="1",I8=4),"48%",IF(AND(F3="1",I8=3),"53%",IF(AND(F3="1",I8=2),"60%",IF(AND(F3="2",I8=4),"65%",IF(AND(F3="2",I8=3),"70%",IF(AND(F3="2",I8=2),"70%"))))))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top