| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Too many arguements?
I have a formula I'm trying to get to work. Here is what I have so far (it
doesn't work yet): =IF(C39=0,"",IF(R39=V,IF(AR39<=0,"Y",IF(AR39=0,"N ")),IF(AA39<=0,"Y",IF(AA39=1,"N")))) I need the formula to look at column C first. If it's a 0 then display nothing in column Q (where this formula is placed). If there's a 1 in column C, I need the formula to look at column R for either a V or an R. If it's a V do this function: R39=V,IF(AR39<=0,"Y",IF(AR39=0,"N" If it's an R, do this function: IF(AA39<=0,"Y",IF(AA39=1,"N" Any help would be great! Thanks. -- Gary |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Too many arguements?
Hi Gary,
You can always build a formula in several (or even many) simple steps and than later, if really necessary (really, really necessary, otherwise, DON'T!), integrate those intermediate formulas into one. Just don't try to do a complex algorithm in one formula; it's *the* safe way to failure. Build the formula in the steps of your verbal explanation (one cell/formula for each) and be sure to always also specify what should be done if a condition is NOT satisfied (I miss that in your specification from the first sentence!) Post again in this thread if you still have problems -- Kind regards, Niek Otten Microsoft MVP - Excel "GHall" wrote in message ... I have a formula I'm trying to get to work. Here is what I have so far (it doesn't work yet): =IF(C39=0,"",IF(R39=V,IF(AR39<=0,"Y",IF(AR39=0,"N ")),IF(AA39<=0,"Y",IF(AA39=1,"N")))) I need the formula to look at column C first. If it's a 0 then display nothing in column Q (where this formula is placed). If there's a 1 in column C, I need the formula to look at column R for either a V or an R. If it's a V do this function: R39=V,IF(AR39<=0,"Y",IF(AR39=0,"N" If it's an R, do this function: IF(AA39<=0,"Y",IF(AA39=1,"N" Any help would be great! Thanks. -- Gary |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Too many arguements?
I see the following problems:
1. When checking R39, the V needs to be in quotation marks. 2. After AR39=0, you have an closing parentheses which aren't needed. So try the following: =IF(C39=0,"",IF(R39="V",IF(AR39<=0,"Y",IF(AR39=0, "N",IF(AA39<=0,"Y",IF(AA39=1,"N")))))) 3. Next you haven't specified what happens when AA39 is between 0 and 1. I suspect you want: =IF(C39=0,"",IF(R39="V",IF(AR39<=0,"Y",IF(AR39=0, "N",IF(AA39<=0,"Y","N"))))) 4. Finally, the check of AA39 will never be done. Prior to that you check to see if AR39 is less than zero, then if it's greater than zero. One of these will always be true. As soon as the If finds something that's true, it finishes, so it will never get around to checking AA39. Do you want something like: =IF(C39=0,"",IF(R39="V",IF(OR(AA39<=0,AR39<=0),"Y" ,"N"))))) Regards, Fred. "GHall" wrote in message ... I have a formula I'm trying to get to work. Here is what I have so far (it doesn't work yet): =IF(C39=0,"",IF(R39=V,IF(AR39<=0,"Y",IF(AR39=0,"N ")),IF(AA39<=0,"Y",IF(AA39=1,"N")))) I need the formula to look at column C first. If it's a 0 then display nothing in column Q (where this formula is placed). If there's a 1 in column C, I need the formula to look at column R for either a V or an R. If it's a V do this function: R39=V,IF(AR39<=0,"Y",IF(AR39=0,"N" If it's an R, do this function: IF(AA39<=0,"Y",IF(AA39=1,"N" Any help would be great! Thanks. -- Gary |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Too many arguements?
Okay, the formulas you presented didn't quite do what I was looking for, but
you did point out a couple problems I was having. I was able to get it to work and do want I need with this: =IF(C21=0,"",IF(R21="V",IF(AR21<=0,"Y",IF(AR21=1, "N")),IF(AA21<=0,"Y",IF(AA21=1,"N")))) Thanks so much for your help! -- Gary Hall "Fred Smith" wrote: I see the following problems: 1. When checking R39, the V needs to be in quotation marks. 2. After AR39=0, you have an closing parentheses which aren't needed. So try the following: =IF(C39=0,"",IF(R39="V",IF(AR39<=0,"Y",IF(AR39=0, "N",IF(AA39<=0,"Y",IF(AA39=1,"N")))))) 3. Next you haven't specified what happens when AA39 is between 0 and 1. I suspect you want: =IF(C39=0,"",IF(R39="V",IF(AR39<=0,"Y",IF(AR39=0, "N",IF(AA39<=0,"Y","N"))))) 4. Finally, the check of AA39 will never be done. Prior to that you check to see if AR39 is less than zero, then if it's greater than zero. One of these will always be true. As soon as the If finds something that's true, it finishes, so it will never get around to checking AA39. Do you want something like: =IF(C39=0,"",IF(R39="V",IF(OR(AA39<=0,AR39<=0),"Y" ,"N"))))) Regards, Fred. "GHall" wrote in message ... I have a formula I'm trying to get to work. Here is what I have so far (it doesn't work yet): =IF(C39=0,"",IF(R39=V,IF(AR39<=0,"Y",IF(AR39=0,"N ")),IF(AA39<=0,"Y",IF(AA39=1,"N")))) I need the formula to look at column C first. If it's a 0 then display nothing in column Q (where this formula is placed). If there's a 1 in column C, I need the formula to look at column R for either a V or an R. If it's a V do this function: R39=V,IF(AR39<=0,"Y",IF(AR39=0,"N" If it's an R, do this function: IF(AA39<=0,"Y",IF(AA39=1,"N" Any help would be great! Thanks. -- Gary |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Too many arguements?
Thanks for your assist. With your advice and the information presented by Fre
I was able to get my formula to work. Thanks for the post. Breaking it up helped me see where the problem was. -- Gary Hall "Niek Otten" wrote: Hi Gary, You can always build a formula in several (or even many) simple steps and than later, if really necessary (really, really necessary, otherwise, DON'T!), integrate those intermediate formulas into one. Just don't try to do a complex algorithm in one formula; it's *the* safe way to failure. Build the formula in the steps of your verbal explanation (one cell/formula for each) and be sure to always also specify what should be done if a condition is NOT satisfied (I miss that in your specification from the first sentence!) Post again in this thread if you still have problems -- Kind regards, Niek Otten Microsoft MVP - Excel "GHall" wrote in message ... I have a formula I'm trying to get to work. Here is what I have so far (it doesn't work yet): =IF(C39=0,"",IF(R39=V,IF(AR39<=0,"Y",IF(AR39=0,"N ")),IF(AA39<=0,"Y",IF(AA39=1,"N")))) I need the formula to look at column C first. If it's a 0 then display nothing in column Q (where this formula is placed). If there's a 1 in column C, I need the formula to look at column R for either a V or an R. If it's a V do this function: R39=V,IF(AR39<=0,"Y",IF(AR39=0,"N" If it's an R, do this function: IF(AA39<=0,"Y",IF(AA39=1,"N" Any help would be great! Thanks. -- Gary |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Too many arguements?
Glad you got it working. As an observation, the formula doesn't cover the
cases where AR21 is between 0 and 1. Same problem with AA21. If you want to handle these cases, try: =IF(C21=0,"",IF(R21="V",IF(AR21<=0,"Y","N"),IF(AA2 1<=0,"Y","N"))) "GHall" wrote in message ... Okay, the formulas you presented didn't quite do what I was looking for, but you did point out a couple problems I was having. I was able to get it to work and do want I need with this: =IF(C21=0,"",IF(R21="V",IF(AR21<=0,"Y",IF(AR21=1, "N")),IF(AA21<=0,"Y",IF(AA21=1,"N")))) Thanks so much for your help! -- Gary Hall "Fred Smith" wrote: I see the following problems: 1. When checking R39, the V needs to be in quotation marks. 2. After AR39=0, you have an closing parentheses which aren't needed. So try the following: =IF(C39=0,"",IF(R39="V",IF(AR39<=0,"Y",IF(AR39=0, "N",IF(AA39<=0,"Y",IF(AA39=1,"N")))))) 3. Next you haven't specified what happens when AA39 is between 0 and 1. I suspect you want: =IF(C39=0,"",IF(R39="V",IF(AR39<=0,"Y",IF(AR39=0, "N",IF(AA39<=0,"Y","N"))))) 4. Finally, the check of AA39 will never be done. Prior to that you check to see if AR39 is less than zero, then if it's greater than zero. One of these will always be true. As soon as the If finds something that's true, it finishes, so it will never get around to checking AA39. Do you want something like: =IF(C39=0,"",IF(R39="V",IF(OR(AA39<=0,AR39<=0),"Y" ,"N"))))) Regards, Fred. "GHall" wrote in message ... I have a formula I'm trying to get to work. Here is what I have so far (it doesn't work yet): =IF(C39=0,"",IF(R39=V,IF(AR39<=0,"Y",IF(AR39=0,"N ")),IF(AA39<=0,"Y",IF(AA39=1,"N")))) I need the formula to look at column C first. If it's a 0 then display nothing in column Q (where this formula is placed). If there's a 1 in column C, I need the formula to look at column R for either a V or an R. If it's a V do this function: R39=V,IF(AR39<=0,"Y",IF(AR39=0,"N" If it's an R, do this function: IF(AA39<=0,"Y",IF(AA39=1,"N" Any help would be great! Thanks. -- Gary |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Too many arguements?
Yeah, I see that. But the 0 and 1 are just code numbers anyway, will never be
any other number. Thanks for pointing it out though! :) You two were a big help with this. -- Gary Hall "Fred Smith" wrote: Glad you got it working. As an observation, the formula doesn't cover the cases where AR21 is between 0 and 1. Same problem with AA21. If you want to handle these cases, try: =IF(C21=0,"",IF(R21="V",IF(AR21<=0,"Y","N"),IF(AA2 1<=0,"Y","N"))) "GHall" wrote in message ... Okay, the formulas you presented didn't quite do what I was looking for, but you did point out a couple problems I was having. I was able to get it to work and do want I need with this: =IF(C21=0,"",IF(R21="V",IF(AR21<=0,"Y",IF(AR21=1, "N")),IF(AA21<=0,"Y",IF(AA21=1,"N")))) Thanks so much for your help! -- Gary Hall "Fred Smith" wrote: I see the following problems: 1. When checking R39, the V needs to be in quotation marks. 2. After AR39=0, you have an closing parentheses which aren't needed. So try the following: =IF(C39=0,"",IF(R39="V",IF(AR39<=0,"Y",IF(AR39=0, "N",IF(AA39<=0,"Y",IF(AA39=1,"N")))))) 3. Next you haven't specified what happens when AA39 is between 0 and 1. I suspect you want: =IF(C39=0,"",IF(R39="V",IF(AR39<=0,"Y",IF(AR39=0, "N",IF(AA39<=0,"Y","N"))))) 4. Finally, the check of AA39 will never be done. Prior to that you check to see if AR39 is less than zero, then if it's greater than zero. One of these will always be true. As soon as the If finds something that's true, it finishes, so it will never get around to checking AA39. Do you want something like: =IF(C39=0,"",IF(R39="V",IF(OR(AA39<=0,AR39<=0),"Y" ,"N"))))) Regards, Fred. "GHall" wrote in message ... I have a formula I'm trying to get to work. Here is what I have so far (it doesn't work yet): =IF(C39=0,"",IF(R39=V,IF(AR39<=0,"Y",IF(AR39=0,"N ")),IF(AA39<=0,"Y",IF(AA39=1,"N")))) I need the formula to look at column C first. If it's a 0 then display nothing in column Q (where this formula is placed). If there's a 1 in column C, I need the formula to look at column R for either a V or an R. If it's a V do this function: R39=V,IF(AR39<=0,"Y",IF(AR39=0,"N" If it's an R, do this function: IF(AA39<=0,"Y",IF(AA39=1,"N" Any help would be great! Thanks. -- Gary |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| nested IF - too many arguements. | Excel Worksheet Functions | |||
| how can i have more than 30 arguements in a SUM | Excel Discussion (Misc queries) | |||
| Too many arguements | Excel Discussion (Misc queries) | |||
| Formula using IF, AND and OR Arguements | Excel Worksheet Functions | |||
| NPV calc with more than 29 arguements? | Excel Worksheet Functions | |||
Linear Mode
