How do you apply criteria in order of preference?

[DLL]

For example:

1.If bad number is submitted
2. If any score is 500
3.If any score is below 100
4. If two column's average 200...

I can do the formula's to give the above information but I do not know how
to apply the above in order of preference as in 1, 2, 3, 4 (above) Thanks for
any help

 

[Guest]

One way;

=IF(NOT(ISNUMBER(TEST)),"Entry error",IF(TEST=500,"SCORE = 500",IF(TEST<100,
"Score is less than 100",IF(AVERAGE(TEST)=200,"Average is 200","Something
Else"))))

*Note that excel has a limit of seven nested statements.
Hope this helps.
DK

 

[Shane Devenshire]

Hi,

are you writing a formula, creating a criteria for a filter, or creating a
conditional format or data validation? Or maybe you are writing a macro?
What do you mean by order of preference? if bad number is submitted what do
you want to do. And what do you want to do if each of the three other
conditions succeed or fail? What version of Excel are you using?

 

[Satyendra_Haldaur]

=IF(OR(NOT(ISNUMBER(A1)),A1=500,AVERAGE(A2,B2)<200,A1<100),"False",A1)