If(AND formula

[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%"))))))

 

[Fred Smith]

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

 

[arealbasketcase]

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

 

[Joe User]

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

 

[Joe User]

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)

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

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%"))))))

 

[Wildwood]

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

 

[Wildwood]

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

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)

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

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%"))))))

 

[Joe User]

neither of your suggestions worked

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

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

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)

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

:
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%"))))))

 

[Fred Smith]

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

 

[Joe User]

neither of your suggestions worked.

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

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

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)

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

:
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%"))))))