Data Validation

[Ralph]

I do score keeping at a tournment in XL. The kids scores
and errors are recorded. I want to have some
automatic "keystroke mistake" checker. The scores can be -
20 to 90 in increments of 10 but only certain # of errors
are valid with each score; see below. How can I have xl
check that the score and error combination is correct
without using a ton of IF statements.
Score Error
90 0
80 1
70 2
60 0 or 2
50 1 or 3
40 0 or 2
30 1 or 3
and so on

 

[Biff]

Hi Ralph!

"without using a ton of IF statements"

Use a ton of OR AND statements!

=OR(AND(A1=90,B1=0),AND(A1=80,B1=1),AND(A1=70,B1=2),AND(OR
(B1=0,B1=2),A1=60),AND(OR(B1=1,B1=3),A1=50),AND(OR
(B1=0,B1=2),A1=40),AND(OR(B1=1,B1=3),A1=30))

That formula covers the scores from 90 to 30. Just follow
the same logic for the remainder of the scores and add
them to the formula. It will be quite long but it will
work!

I'm sure there is a "smaller" solution but this one is
simple and only took a few minutes.

Biff