| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error in Formula ??
I am getting an error in this formula:
=IF(AND(C5<"Non U/G",C5<"Delta",C7V2,C7<W2,C8W2),"0.75",IF(AND(C5 ="Delta",C7V2,C7<W2,C8W2),"0.5",IF(AND(C5<"N on U/G",C5<"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delt a",C7<V2,C8<V2),"1",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8=W2),"0.75",IF(AND(C5="Del ta",C7<V2,C8=W2),"0.5",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8V2,C8<=W2),"0.75",IF(AND(C 7<V2,C8V2,C8<=W2,"0.5","0")))))))) Can anyone tell me why? If i change it by dropping off the last bit(but is needed) the error is not present: =IF(AND(C5<"Non U/G",C5<"Delta",C7V2,C7<W2,C8W2),"0.75",IF(AND(C5 ="Delta",C7V2,C7<W2,C8W2),"0.5",IF(AND(C5<"N on U/G",C5<"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delt a",C7<V2,C8<V2),"1",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8=W2),"0.75",IF(AND(C5="Del ta",C7<V2,C8=W2),"0.5",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8V2,C8<=W2),"0.75","0"))))) )) Is it becase it has reached the limit of the Formula? Is there a way to simplify the above? Regards Corey |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Error in Formula ??
=IF(AND(C5<"Non U/G",C5<"Delta",C7V2,C7<W2,C8W2),"0.75",IF(AND(C5 ="Delta",C7V2,C7<W2,C8W2),"0.5",IF(AND(C5<"N on U/G",C5<"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delt a",C7<V2,C8<V2),"1",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8=W2),"0.75",IF(AND(C5="Del ta",C7<V2,C8=W2),"0.5",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8V2,C8<=W2),"0.75",IF(AND(C 7<V2,C8V2,C8<=W2),"0.5","0"))))))))
Sory the actual code with the error is above, I missed a Bracket. Error is still present though. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Error in Formula ??
You have one too many nesting levels at eight and also, you either have an
unintended comma or are missing a condition in the following And function: IF(AND(,C5="Delta",C7<V2,C8<V2)€¦ Note that this will always return False as currently written: Regards, Greg "Corey" wrote: I am getting an error in this formula: =IF(AND(C5<"Non U/G",C5<"Delta",C7V2,C7<W2,C8W2),"0.75",IF(AND(C5 ="Delta",C7V2,C7<W2,C8W2),"0.5",IF(AND(C5<"N on U/G",C5<"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delt a",C7<V2,C8<V2),"1",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8=W2),"0.75",IF(AND(C5="Del ta",C7<V2,C8=W2),"0.5",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8V2,C8<=W2),"0.75",IF(AND(C 7<V2,C8V2,C8<=W2,"0.5","0")))))))) Can anyone tell me why? If i change it by dropping off the last bit(but is needed) the error is not present: =IF(AND(C5<"Non U/G",C5<"Delta",C7V2,C7<W2,C8W2),"0.75",IF(AND(C5 ="Delta",C7V2,C7<W2,C8W2),"0.5",IF(AND(C5<"N on U/G",C5<"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delt a",C7<V2,C8<V2),"1",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8=W2),"0.75",IF(AND(C5="Del ta",C7<V2,C8=W2),"0.5",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8V2,C8<=W2),"0.75","0"))))) )) Is it becase it has reached the limit of the Formula? Is there a way to simplify the above? Regards Corey |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Error in Formula ??
On Thu, 3 Aug 2006 08:50:14 +1000, "Corey"
wrote: =IF(AND(C5<"Non U/G",C5<"Delta",C7V2,C7<W2,C8W2),"0.75",IF(AND(C5 ="Delta",C7V2,C7<W2,C8W2),"0.5",IF(AND(C5<"N on U/G",C5<"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delt a",C7<V2,C8<V2),"1",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8=W2),"0.75",IF(AND(C5="Del ta",C7<V2,C8=W2),"0.5",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8V2,C8<=W2),"0.75",IF(AND(C 7<V2,C8V2,C8<=W2),"0.5","0")))))))) Sory the actual code with the error is above, I missed a Bracket. Error is still present though. From Excel Specifications and limits: Nested levels of functions 7 You have 8 nested levels. --ron |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Error in Formula ??
Thanks for the reply guys,
So what options do i have if there is too many nestings? Is there a different formula syntax i can use? -- Regards Corey "Greg Wilson" wrote in message ... You have one too many nesting levels at eight and also, you either have an unintended comma or are missing a condition in the following And function: IF(AND(,C5="Delta",C7<V2,C8<V2). Note that this will always return False as currently written: Regards, Greg "Corey" wrote: I am getting an error in this formula: =IF(AND(C5<"Non U/G",C5<"Delta",C7V2,C7<W2,C8W2),"0.75",IF(AND(C5 ="Delta",C7V2,C7<W2,C8W2),"0.5",IF(AND(C5<"N on U/G",C5<"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delt a",C7<V2,C8<V2),"1",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8=W2),"0.75",IF(AND(C5="Del ta",C7<V2,C8=W2),"0.5",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8V2,C8<=W2),"0.75",IF(AND(C 7<V2,C8V2,C8<=W2,"0.5","0")))))))) Can anyone tell me why? If i change it by dropping off the last bit(but is needed) the error is not present: =IF(AND(C5<"Non U/G",C5<"Delta",C7V2,C7<W2,C8W2),"0.75",IF(AND(C5 ="Delta",C7V2,C7<W2,C8W2),"0.5",IF(AND(C5<"N on U/G",C5<"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delt a",C7<V2,C8<V2),"1",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8=W2),"0.75",IF(AND(C5="Del ta",C7<V2,C8=W2),"0.5",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8V2,C8<=W2),"0.75","0"))))) )) Is it becase it has reached the limit of the Formula? Is there a way to simplify the above? Regards Corey |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Error in Formula ??
Perhaps this:
=If(C5 = "Delta", If(And(C7V2, C7<W2, C8W2), 0.5, If(And(C7<V2, C8<V2), 1, If(And(C7<V2, C8=W2), 0.5, 0))), If(C5 = "Non U/G", If(And(C7<V2, C8V2, C8<=W2), 0.5, 0), If(And(C7V2, C7<W2, C8W2), 0.75, If(And(C7<V2, C8<V2), 1.5, If(And(C7<V2, C8=W2), 0.75, IF(And(C7<V2, C8V2, C8<=W2), 0.75, 0)))))) I believe the above reconstructed formula meets the logic you specified. It has one less nesting level and therefore should work. Regards, Greg "Corey" wrote: Thanks for the reply guys, So what options do i have if there is too many nestings? Is there a different formula syntax i can use? -- Regards Corey "Greg Wilson" wrote in message ... You have one too many nesting levels at eight and also, you either have an unintended comma or are missing a condition in the following And function: IF(AND(,C5="Delta",C7<V2,C8<V2). Note that this will always return False as currently written: Regards, Greg "Corey" wrote: I am getting an error in this formula: =IF(AND(C5<"Non U/G",C5<"Delta",C7V2,C7<W2,C8W2),"0.75",IF(AND(C5 ="Delta",C7V2,C7<W2,C8W2),"0.5",IF(AND(C5<"N on U/G",C5<"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delt a",C7<V2,C8<V2),"1",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8=W2),"0.75",IF(AND(C5="Del ta",C7<V2,C8=W2),"0.5",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8V2,C8<=W2),"0.75",IF(AND(C 7<V2,C8V2,C8<=W2,"0.5","0")))))))) Can anyone tell me why? If i change it by dropping off the last bit(but is needed) the error is not present: =IF(AND(C5<"Non U/G",C5<"Delta",C7V2,C7<W2,C8W2),"0.75",IF(AND(C5 ="Delta",C7V2,C7<W2,C8W2),"0.5",IF(AND(C5<"N on U/G",C5<"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delt a",C7<V2,C8<V2),"1",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8=W2),"0.75",IF(AND(C5="Del ta",C7<V2,C8=W2),"0.5",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8V2,C8<=W2),"0.75","0"))))) )) Is it becase it has reached the limit of the Formula? Is there a way to simplify the above? Regards Corey |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Creating a check mark box | Setting up and Configuration of Excel | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Linear Mode
