|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 23rd, 2010, 09:39 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
If(AND formula
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(F 3="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(A ND(F3="2",I8=2),"70%")))))) 
|
|
#2
March 23rd, 2010, 09:55 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
If(AND formula
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%,I F(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 "Wildwood" wrote in 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(F 3="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(A ND(F3="2",I8=2),"70%"))))))
|
|
#3
March 23rd, 2010, 10:18 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
If(AND formula
try this:
=IF(AND(F3=1,I8=4),"48%",IF(AND(F3=1,I8=3),"53%",I F(AND(F3=1,I8=2),"60",IF(AND(F3=2,I8=4),"65%",IF(A ND(F3=2,I8=3),"70",IF(AND(F3=2,I8=2),"70",0)))))) Hope it helps "Wildwood" wrote: 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(F 3="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(A ND(F3="2",I8=2),"70%"))))))
|
|
#4
March 23rd, 2010, 11:49 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
If(AND formula
"Wildwood" wrote:
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 ----- "Wildwood" wrote: 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(F 3="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(A ND(F3="2",I8=2),"70%"))))))
|
|
#5
March 24th, 2010, 12:29 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
If(AND formula
Errata (typo)....
I wrote: 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 ----- "Joe User" wrote: "Wildwood" wrote: 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 ----- "Wildwood" wrote: 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(F 3="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(A ND(F3="2",I8=2),"70%"))))))
|
|
#6
March 24th, 2010, 08:26 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
If(AND formula
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))))))) "Fred Smith" wrote: 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%,I F(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 "Wildwood" wrote in 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(F 3="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(A ND(F3="2",I8=2),"70%")))))) .
|
|
#7
March 24th, 2010, 08:56 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
If(AND formula
I'm using Excel 2007 - - but neither of your suggestions worked......
"Joe User" wrote: Errata (typo).... I wrote: 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 ----- "Joe User" wrote: "Wildwood" wrote: 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 ----- "Wildwood" wrote: 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(F 3="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(A ND(F3="2",I8=2),"70%"))))))
|
|
#8
March 24th, 2010, 10:58 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
If(AND formula
"Wildwood" wrote:
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 ----- "Wildwood" wrote: I'm using Excel 2007 - - but neither of your suggestions worked...... "Joe User" wrote: Errata (typo).... I wrote: 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 ----- "Joe User" wrote: "Wildwood" wrote: 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 ----- "Wildwood" wrote: 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(F 3="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(A ND(F3="2",I8=2),"70%"))))))
|
|
#9
March 24th, 2010, 11:34 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
If(AND formula
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" wrote in message ... 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))))))) "Fred Smith" wrote: 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%,I F(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 "Wildwood" wrote in 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(F 3="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(A ND(F3="2",I8=2),"70%")))))) .
|
|
#10
March 24th, 2010, 11:52 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
If(AND formula
"Wildwood" wrote:
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 ----- "Wildwood" wrote: I'm using Excel 2007 - - but neither of your suggestions worked...... "Joe User" wrote: Errata (typo).... I wrote: 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 ----- "Joe User" wrote: "Wildwood" wrote: 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 ----- "Wildwood" wrote: 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(F 3="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(A ND(F3="2",I8=2),"70%"))))))
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
