Formula in Validation

[Salza]

Hi all,

The formula below is used for VALIDATION in one of the cell.
I used Allow - Custom - Formula.

=((AF7>=0)*(AF7<=100)+(CODE(AF7)>=65)*(CODE(AF7)<=69))

In that cell, users can only type number 0 to 100 or A to E in capital
letters.

Can someone interpret the formula for me?

I want to add alphabet T and alphabet O in the list.
How do I change the formula?

Thanks.

 

[Bob Phillips]

Salza,

Basically it is checking if AF7 is a number in the range 0-100, or a letter
A-E, and returns 0 if not, 1 if so.

This part
(AF7>=0)*(AF7<=100)
will test AF7 twice, returning a TRUE or FALSE for each test. The two
results are multiplied, which give a number 0 or 1 (FALSE*FALSE=0,
FALSE*TRUE=0, TRUE*FALSE=0, TRUE*TRUE=1).

Similarly
(CODE(AF7)>=65)*(CODE(AF7)<=69))
will also return a 0 or a 1.

As it can only be a letter or a number, not both, adding the two parts can
only give 0 or 1.

Try this to include T & O

=((AF7>=0)*(AF7<=100)+(CODE(AF7)>=65)*(OR(CODE(AF7)<=69,CODE(AF7)={79,84})))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[J.E. McGimpsey]

Breaking it down:

(AF7>=0)

returns a boolean (true/false) value depending on the value of A7.
Likewise

(AF7<=100)

will return TRUE if AF7 is less than or equal to 100, FALSE
otherwise. When multiplying TRUE/FALSE values, XL first converts the
value into 1/0, respectively, so if both conditions are TRUE,

(AF7>=0)*(AF7<=100)) ==> (TRUE)*(TRUE) ===> 1 * 1 = 1

If AF7 were outside that range, say 1000, then

(AF7>=0)*(AF7<=100)) ==> (TRUE)*(FALSE) ===> 1 * 0 = 0

CODE(AF7) returns the ASCII code of the first character in AF7.
Capital letters begin with 65 for A, 66 for (B), etc. The
conditionals work similarly to the above. So if AF7 started with
"C", say:

(CODE(AF7)>=65)*(CODE(AF7)<=69) ==> (TRUE)*(TRUE) ==> 1*1 = 1

Likewise the product if the first character in AF7 is NOT between A
& E will return 0.

Adding the two:

AF7=50: (TRUE)*(TRUE) + (FALSE)*(FALSE) = 1*1 + 0*0 = 1
AF7=1000: (TRUE)*(FALSE) + (FALSE)*(FALSE) = 1*0 + 0*0 = 0
AF7="ABCF": (FALSE)*(FALSE) + (TRUE)*TRUE) = 0*0 + (1*1) = 1
AF7="FABC": (FALSE)*(FALSE) + (FALSE)*(FALSE) = 0*0 + 0*0 = 0

When evaluating a boolean condition, XL interprets a zero value as
FALSE and any other numeric value as TRUE, so the first and third
result would evaluate to TRUE and the others to FALSE.

To Add "T" and "O", you'll need to add terms to the formula that
make the end result non-zero if either of those letters is the first
character. One way:

=((AF7>=0)*(AF7<=100)+(CODE(AF7)>=65)*(CODE(AF7)<=69)) +
(CODE(AF7)=79) + (CODE(AF7)=84)

 

[J.E. McGimpsey]

Don't think this will work - Validation doesn't accept array
constants.

Try


=((AF7>=0)*(AF7<=100)+(CODE(AF7)>=65)*(OR(CODE(AF7)<=69,CODE(AF7)=79,
CODE(AF7)=84)))

instead

 

[Bob Phillips]

Dammit, I only tried it in a cell. This is a version without array constants

=((AF7>=0)*(AF7<=100)+(CODE(AF7)>=65)*(OR(CODE(AF7)<=69,CODE(AF7)=79,CODE(AF
7)=84)))


Bob