Determine whether a value is in a range of cells
 

I'm new ot this group and moderately experienced with Excel. Here is my
question.

I have a range of cells where I need to determine if a value is located
within the range. My cumbersome formula follows. I need to simplify the
formula in order to get it under the 255 character limit.

Example follows:

=IF($B$250,"n/a",(IF(OR(AE$17=$C25,AE$17=$D25,AE$17=$E25,AE$17=$ F25,AE$17=$G25,AE$17=$H25,AE$17=$I25,AE$17=$J25,AE $17=$K25,AE$17=$L25,AE$17=$M25,AE$17=$N25,AE$17=$O 25,AE$17=$P25,AE$17=$Q25,AE$17=$R25,AE$17=$S25,AE$ 17=$T25,AE$17=$U25,AE$17=$V25,AE$17=$W25,AE$17=$X2 5,AE$17=$Y25,AE$17=$Z25,AE$17=$AA25,AE$17=$AB25,AE $17=$AC25)=TRUE,"",AE$17)))

It is the OR function that I am trying to simplify: In the formula I am
asking if AE$17 is equal to any of the cells in the range $C25:$AC25.
There has to be a simpler way to do this.

I could use help on this from anyone.

Carl

Determine whether a value is in a range of cells
 

Hi!

Try this:

=IF($B$250,"N/A",IF(COUNTIF($C25:$AC25,AE$17),"",AE$17))

I need to simplify the formula in order to get it under the 255 character
limit.

The length limit of formulas is 1024 chars.

Biff

"Carl" wrote in message
oups.com...
I'm new ot this group and moderately experienced with Excel. Here is my
question.

I have a range of cells where I need to determine if a value is located
within the range. My cumbersome formula follows. I need to simplify the
formula in order to get it under the 255 character limit.

Example follows:

=IF($B$250,"n/a",(IF(OR(AE$17=$C25,AE$17=$D25,AE$17=$E25,AE$17=$ F25,AE$17=$G25,AE$17=$H25,AE$17=$I25,AE$17=$J25,AE $17=$K25,AE$17=$L25,AE$17=$M25,AE$17=$N25,AE$17=$O 25,AE$17=$P25,AE$17=$Q25,AE$17=$R25,AE$17=$S25,AE$ 17=$T25,AE$17=$U25,AE$17=$V25,AE$17=$W25,AE$17=$X2 5,AE$17=$Y25,AE$17=$Z25,AE$17=$AA25,AE$17=$AB25,AE $17=$AC25)=TRUE,"",AE$17)))

It is the OR function that I am trying to simplify: In the formula I am
asking if AE$17 is equal to any of the cells in the range $C25:$AC25.
There has to be a simpler way to do this.

I could use help on this from anyone.

Carl

Determine whether a value is in a range of cells
 

Thanks much for the help. It worked great. The 255 limit that I was
referring to was the character limit that seemed to exist when using
VBA CovertFormula. Now that my cells are under 255 characters the VBA
command runds fine.
Carl
Biff wrote:
Hi!

Try this:

=IF($B$250,"N/A",IF(COUNTIF($C25:$AC25,AE$17),"",AE$17))

I need to simplify the formula in order to get it under the 255 character
limit.

The length limit of formulas is 1024 chars.

Biff

"Carl" wrote in message
oups.com...
I'm new ot this group and moderately experienced with Excel. Here is my
question.

I have a range of cells where I need to determine if a value is located
within the range. My cumbersome formula follows. I need to simplify the
formula in order to get it under the 255 character limit.

Example follows:

=IF($B$250,"n/a",(IF(OR(AE$17=$C25,AE$17=$D25,AE$17=$E25,AE$17=$ F25,AE$17=$G25,AE$17=$H25,AE$17=$I25,AE$17=$J25,AE $17=$K25,AE$17=$L25,AE$17=$M25,AE$17=$N25,AE$17=$O 25,AE$17=$P25,AE$17=$Q25,AE$17=$R25,AE$17=$S25,AE$ 17=$T25,AE$17=$U25,AE$17=$V25,AE$17=$W25,AE$17=$X2 5,AE$17=$Y25,AE$17=$Z25,AE$17=$AA25,AE$17=$AB25,AE $17=$AC25)=TRUE,"",AE$17)))

It is the OR function that I am trying to simplify: In the formula I am
asking if AE$17 is equal to any of the cells in the range $C25:$AC25.
There has to be a simpler way to do this.

I could use help on this from anyone.

Carl