|
Problem with nested IF_OR statement
I am using a nested IF_OR statement to evaluate a list of values to see if
any of them are 100, and to return YES if they are, and NO if they are not. When I enter the formula using the formula palette, the value shown in the palette is NO which is correct, however #VALUE! is displayed in the cell. I have checked and the formula is entered correctly. I am completely perplexed. Any help would be appreciated. |
Post your formula.
-- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "DOOGIE" wrote in message ... I am using a nested IF_OR statement to evaluate a list of values to see if any of them are 100, and to return YES if they are, and NO if they are not. When I enter the formula using the formula palette, the value shown in the palette is NO which is correct, however #VALUE! is displayed in the cell. I have checked and the formula is entered correctly. I am completely perplexed. Any help would be appreciated. |
=IF(OR(B55:B175100),"YES","NO")
"RagDyeR" wrote: Post your formula. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "DOOGIE" wrote in message ... I am using a nested IF_OR statement to evaluate a list of values to see if any of them are 100, and to return YES if they are, and NO if they are not. When I enter the formula using the formula palette, the value shown in the palette is NO which is correct, however #VALUE! is displayed in the cell. I have checked and the formula is entered correctly. I am completely perplexed. Any help would be appreciated. |
Do you realize that your formula is an *array* formula?
-- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "DOOGIE" wrote in message ... =IF(OR(B55:B175100),"YES","NO") "RagDyeR" wrote: Post your formula. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "DOOGIE" wrote in message ... I am using a nested IF_OR statement to evaluate a list of values to see if any of them are 100, and to return YES if they are, and NO if they are not. When I enter the formula using the formula palette, the value shown in the palette is NO which is correct, however #VALUE! is displayed in the cell. I have checked and the formula is entered correctly. I am completely perplexed. Any help would be appreciated. |
No, I did not realize that. I tried it and it works! Excel has a lot of odd
quirks. Thank you very much! "Ragdyer" wrote: Do you realize that your formula is an *array* formula? -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "DOOGIE" wrote in message ... =IF(OR(B55:B175100),"YES","NO") "RagDyeR" wrote: Post your formula. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "DOOGIE" wrote in message ... I am using a nested IF_OR statement to evaluate a list of values to see if any of them are 100, and to return YES if they are, and NO if they are not. When I enter the formula using the formula palette, the value shown in the palette is NO which is correct, however #VALUE! is displayed in the cell. I have checked and the formula is entered correctly. I am completely perplexed. Any help would be appreciated. |
There's really nothing odd about array formulas.
They have their place, where there might not be any other way to accomplish a calculation. In your case however, you could use a non-array formula to do the same job: =IF(COUNTIF(B55:B175,"100")0,"YES","NO") -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "DOOGIE" wrote in message ... No, I did not realize that. I tried it and it works! Excel has a lot of odd quirks. Thank you very much! "Ragdyer" wrote: Do you realize that your formula is an *array* formula? -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "DOOGIE" wrote in message ... =IF(OR(B55:B175100),"YES","NO") "RagDyeR" wrote: Post your formula. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "DOOGIE" wrote in message ... I am using a nested IF_OR statement to evaluate a list of values to see if any of them are 100, and to return YES if they are, and NO if they are not. When I enter the formula using the formula palette, the value shown in the palette is NO which is correct, however #VALUE! is displayed in the cell. I have checked and the formula is entered correctly. I am completely perplexed. Any help would be appreciated. |
Thanks again!
"RagDyeR" wrote: There's really nothing odd about array formulas. They have their place, where there might not be any other way to accomplish a calculation. In your case however, you could use a non-array formula to do the same job: =IF(COUNTIF(B55:B175,"100")0,"YES","NO") -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "DOOGIE" wrote in message ... No, I did not realize that. I tried it and it works! Excel has a lot of odd quirks. Thank you very much! "Ragdyer" wrote: Do you realize that your formula is an *array* formula? -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "DOOGIE" wrote in message ... =IF(OR(B55:B175100),"YES","NO") "RagDyeR" wrote: Post your formula. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "DOOGIE" wrote in message ... I am using a nested IF_OR statement to evaluate a list of values to see if any of them are 100, and to return YES if they are, and NO if they are not. When I enter the formula using the formula palette, the value shown in the palette is NO which is correct, however #VALUE! is displayed in the cell. I have checked and the formula is entered correctly. I am completely perplexed. Any help would be appreciated. |
And thank you for the feed-back.
-- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "DOOGIE" wrote in message ... Thanks again! "RagDyeR" wrote: There's really nothing odd about array formulas. They have their place, where there might not be any other way to accomplish a calculation. In your case however, you could use a non-array formula to do the same job: =IF(COUNTIF(B55:B175,"100")0,"YES","NO") -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "DOOGIE" wrote in message ... No, I did not realize that. I tried it and it works! Excel has a lot of odd quirks. Thank you very much! "Ragdyer" wrote: Do you realize that your formula is an *array* formula? -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "DOOGIE" wrote in message ... =IF(OR(B55:B175100),"YES","NO") "RagDyeR" wrote: Post your formula. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "DOOGIE" wrote in message ... I am using a nested IF_OR statement to evaluate a list of values to see if any of them are 100, and to return YES if they are, and NO if they are not. When I enter the formula using the formula palette, the value shown in the palette is NO which is correct, however #VALUE! is displayed in the cell. I have checked and the formula is entered correctly. I am completely perplexed. Any help would be appreciated. |
| All times are GMT +1. The time now is 01:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com