To accept manual data or return a default value based on condition.

[Gajendran Yadhav]

Friends,

I'm working on a worksheet where I've a plenty of data to be validated.

I'm having the column N for entering the monetary value ranging from "0" to actual value.

If column P is equal to "FIR" or "Criminal", then column N should accept only "0" as its value.
if column P is not equal to "FIR" or "Criminal", then it can accept any value greater than "0"

As of now I'm using the Conditional Formatting to highlight in Red (as error) if the condition is satisfied.
=IF(OR(P5="FIR",P5="CRIMINAL")*AND(N5<>0),TRUE,FALSE)

Row 5 is the first row of the worksheet. Rows 1 - 4 has some headers and table heads.

A simple Data Validation formula would suffice my need. I'm not much of a techie to use VBA Codes...

Any help is much appreciated. TIA

 

[Gajendran Yadhav]

Either formula will work just fine. Yours also checks that it is a number so it wont allow text to be entered.

To check for data in column P try this:

=AND(P2 <> "", IF(OR(P2 = "FIR", P2 = "Criminal"), N2 = 0, ISNUMBER(N2)))

And you could show error message like "Please entere a classification first. If classification is either FIR or Criminal, monetary component should be zero."

Libro1
	N	O	P	Q
1	Monetary Component	By / Against	Classification	Data Validation formula
2	0		FIR	VERDADERO
3	5		FIR	FALSO
4	abc		FIR	FALSO
5	0		Criminal	VERDADERO
6	5		Criminal	FALSO
7	abc		Criminal	FALSO
8	0		CD	VERDADERO
9	5		CD	VERDADERO
10	abc		CD	FALSO
11	0		WC	VERDADERO
12	5		WC	VERDADERO
13	abc		WC	FALSO
14	0			FALSO
15	5			FALSO
16	abc			FALSO
Hoja1

Cell Formulas
Range	Formula
Q2:Q16	Q2	=AND(P2<>"",IF(OR(P2 = "FIR", P2 = "Criminal"), N2 = 0, ISNUMBER(N2)))

Hi Felixstraube,
Thanks for your suggestion to check if column P is not empty.... it works great.

Kudoos man !!

I tried using LEN function instead and it turned out to work as well.

The formula I tried is

=(IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5)))*AND(LEN(P5)<>0)

Thanks for your extended support.

 

[felixstraube]

Thanks for the feedback. Yes LEN will work as well.
But you are using wrong the AND function. In your formula it isn't doing anything the real AND operation comes from the * before the AND.
The proper usage of AND would be:

=AND(IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5)),LEN(P5)<>0)

Or without AND function you could write:

=IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5))*(LEN(P5)<>0)

 

[Gajendran Yadhav]

Thanks for the feedback. Yes LEN will work as well.
But you are using wrong the AND function. In your formula it isn't doing anything the real AND operation comes from the * before the AND.
The proper usage of AND would be:

=AND(IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5)),LEN(P5)<>0)

Or without AND function you could write:

=IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5))*(LEN(P5)<>0)

Dear,

Thanks for your guidance over the usage of AND function. and btw, is there any possibility to add another control where if P5="CD", N5>=2

I tried my best, but couldn't figure to concatenate multiple functions into one.

I will go with this formula which you suggested...

=IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5))*(LEN(P5)<>0)

 

[Gajendran Yadhav]

Thanks for the feedback. Yes LEN will work as well.
But you are using wrong the AND function. In your formula it isn't doing anything the real AND operation comes from the * before the AND.
The proper usage of AND would be:

=AND(IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5)),LEN(P5)<>0)

Or without AND function you could write:

=IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5))*(LEN(P5)<>0)

Dear Felix,

Please ignore my previous reply... I was not able to edit as time expired.

Thanks a lot for your guidance over my concern and in the usage of AND function.

And by the way, I tried adding another condition that if P5="CD", N5=>2. -- Accidentally I got it worked by using the below formula..

I will go with this formula which you suggested...

=(IF(P5="CD",N5>=2,(IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5))*(LEN(P5)<>0))))<>0))))

Any suggestions or comments is much obliged. TIA

 

[felixstraube]

With you new formula (which has to many closing parenthesis) you won't be checking first if you have entered something in column P

In your case, as I understand it you want first to check
1. if there is something entered in Classification
2. Monetary always has to be a number (not text)
3. if Classificaction is FIR or Criminal, monetary only can be 0
4. if Classification is CD, monetary should be equal or greater than 2
5. if Classification is any other value, monetary can be any value

In logic this can be written like this:

Validation = 
    (Class <> "")
    AND 
    (Monet is number)
    (
        ((Class = "FIR") AND (Monet = 0))
        OR
        ((Class = "Criminal") AND (Monet = 0))
        OR
        ((Class = "CD") AND (Monet >= 2))
        OR
        ((Class <> "FIR") AND (Class <> "Criminal") AND (Class <> "CD") AND (Monet Is number))
    )

for it to work in excel you will have to use * as AND operators and + as OR operator like this

= 
    (P2 <> "")
    *
    ISNUMBER(N2)
    *
    (
        ((P2 = "FIR") * (N2 = 0))
        +
        ((P2 = "Criminal") * (N2 = 0))
        +
        ((P2 = "CD") * (N2 >= 2))
        +
        ((P2 <> "FIR") * (P2 <> "Criminal") * (P2 <> "CD"))
    )

Rewrite it to a one line formula:

= (P2 <> "") * ISNUMBER(N2) * (((P2 = "FIR") * (N2 = 0)) + ((P2 = "Criminal") * (N2 = 0)) + ((P2 = "CD") * (N2 >= 2)) + ((P2 <> "FIR") * (P2 <> "Criminal") * (P2 <> "CD")))

Or you can write it with the AND and OR functions:

 =
AND(
    P2<>""
    ,
    ISNUMBER(N2)
    ,
    OR(
        AND(P2="FIR", N2=0),
        AND(P2="Criminal", N2=0),
        AND(P2="CD", N2>=2),
        AND(P2<>"FIR", P2<>"Criminal", P2<>"CD")
    )
)

The same in one line:

 = AND(P2<>"", ISNUMBER(N2), OR(AND(P2="FIR", N2=0), AND(P2="Criminal", N2=0), AND(P2="CD", N2>=2), AND(P2<>"FIR", P2<>"Criminal", P2<>"CD")))

 

[Gajendran Yadhav]

With you new formula (which has to many closing parenthesis) you won't be checking first if you have entered something in column P

In your case, as I understand it you want first to check
1. if there is something entered in Classification
2. Monetary always has to be a number (not text)
3. if Classificaction is FIR or Criminal, monetary only can be 0
4. if Classification is CD, monetary should be equal or greater than 2
5. if Classification is any other value, monetary can be any value

In logic this can be written like this:

Validation =
    (Class <> "")
    AND
    (Monet is number)
    (
        ((Class = "FIR") AND (Monet = 0))
        OR
        ((Class = "Criminal") AND (Monet = 0))
        OR
        ((Class = "CD") AND (Monet >= 2))
        OR
        ((Class <> "FIR") AND (Class <> "Criminal") AND (Class <> "CD") AND (Monet Is number))
    )

for it to work in excel you will have to use * as AND operators and + as OR operator like this

=
    (P2 <> "")
    *
    ISNUMBER(N2)
    *
    (
        ((P2 = "FIR") * (N2 = 0))
        +
        ((P2 = "Criminal") * (N2 = 0))
        +
        ((P2 = "CD") * (N2 >= 2))
        +
        ((P2 <> "FIR") * (P2 <> "Criminal") * (P2 <> "CD"))
    )

Rewrite it to a one line formula:

= (P2 <> "") * ISNUMBER(N2) * (((P2 = "FIR") * (N2 = 0)) + ((P2 = "Criminal") * (N2 = 0)) + ((P2 = "CD") * (N2 >= 2)) + ((P2 <> "FIR") * (P2 <> "Criminal") * (P2 <> "CD")))

Or you can write it with the AND and OR functions:

 =
AND(
    P2<>""
    ,
    ISNUMBER(N2)
    ,
    OR(
        AND(P2="FIR", N2=0),
        AND(P2="Criminal", N2=0),
        AND(P2="CD", N2>=2),
        AND(P2<>"FIR", P2<>"Criminal", P2<>"CD")
    )
)

The same in one line:

 = AND(P2<>"", ISNUMBER(N2), OR(AND(P2="FIR", N2=0), AND(P2="Criminal", N2=0), AND(P2="CD", N2>=2), AND(P2<>"FIR", P2<>"Criminal", P2<>"CD")))

that's real nice of you...

I learnt something new...

 

[felixstraube]

Glad to help. Thanks for the feedback