Determine whether a value is in a range of cells

[Carl]

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$25>0,"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=$O25,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=$X25,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

 

[Biff]

Hi!

Try this:

=IF($B$25>0,"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]

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.