• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

clarification to Data Validation Function

Alaa_Ali

New Member
Hello

I still at an intermediate level in excel, As a way to learn I get some Free Excel Template and try to build them again.

I just need some clarification to this Data validation Function

=IFERROR(IF(SUM(1/COUNTIF(Tbl_Product[Product Name],Tbl_Product[Product Name]))<COUNTA(Tbl_Product[Product Name]),"Duplicate Product Names","No Errors"),"Check Product Names in Product Table")

Why do I need the Sum function, I think the If function is enough?

And I will add a sample file, I want to know how the table extend the bottom borders when I add new product.

Regards
 

Attachments

  • Inventory Sample.xlsx
    136 KB · Views: 4
Hi ,

The formula which is doing the validation and displaying the appropriate message in cell G22 is :

=IFERROR(IF(SUM(1/COUNTIF(Tbl_Product[Product Name],Tbl_Product[Product Name]))<COUNTA(Tbl_Product[Product Name]),"Duplicate Product Names","No Errors"),"Check Product Names in Product Table")

Suppose we consider 2 entries in the Product Name field in the table ; the possibilities are :

1. The two names are the same

2. The two names are different

3. One or both the names are blank

Firstly , the IF function is checking for the results of 2 calculations :

a. SUM(1/COUNTIF(Tbl_Product[Product Name],Tbl_Product[Product Name]))

b. COUNTA(Tbl_Product[Product Name])

The second calculation is straightforward ; it counts the non-blank entries in the field ; if both entries are blank , the result will be 0 , if one is non-blank , the result will be 1 , and if both are non-blank , the result will be 2.

This calculation is independent of whether the two entries are the same or they are different.

The first calculation is slightly more complex ; the COUNTIF function has two parameters , where the first parameter is a range , and the second parameter is usually a scalar or single value ; the COUNTIF function returns the number of occurrences of this single value in the range that is the first parameter.

However , the COUNTIF function can also accept a range as the second parameter , and in this case , the return value is an array which consists of the same number of elements as the second parameter. Each element of the output array gives the number of occurrences of each element of the second parameter in the first parameter.

To explain this more clearly , consider two ranges A33:A42 and B25:B29.

A33:A42 has the values 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10

B25:B29 has the values 3 , 7 , 4 , 3 , 4

The following formula :

=COUNTIF(A33:A42,B25:B29) will return the output array of values :

{1;1;1;1;1}

since each of the elements of the second parameter occurs once in the first parameter. There are 5 elements in the output array because the second parameter has 5 elements in the range B25:B29.

The following formula :

=COUNTIF(B25:B29,A33:A42) will return the output array of values :

{0;0;2;2;0;0;1;0;0;0}


since 1 and 2 do not occur in the first parameter , 3 and 4 each occurs twice , 5 and 6 do not occur , 7 occurs once , while 8 and 9 too do not occur in the first parameter. There are 10 elements in the output array because the second parameter has 10 elements in the range A33:A42.

Now that we have understood this , let us consider the three cases that we listed earlier :

1. When the two names are the same , the COUNTIF function will return an array of values {2;2} since each name occurs twice , because the first parameter and the second parameter are the same range.

When we take the reciprocal of this by using :

1/COUNTIF(Tbl_Product[Product Name],Tbl_Product[Product Name]))

we get an array :

1/{2 ; 2}

which equates to :

{0.5 ; 0.5}

Now you can understand why the SUM function is necessary as a wrapper around this ; we need a SUM function to add together all the elements of the result array ; in this case adding 0.5 and 0.5 gives us 1.

The IF statement now checks and finds that 1 is less than 2 and displays the error message :

Duplicate Product Names

2. When the two names are different , the COUNTIF function will return an array of values {1;1} since each name occurs only once , because the first parameter and the second parameter are the same range.

When we take the reciprocal of this by using :

1/COUNTIF(Tbl_Product[Product Name],Tbl_Product[Product Name]))

we get an array :

1/{1 ; 1}

which equates to :

{1 ; 1}

In this case adding 1 and 1 gives us 2.

The IF statement now checks and finds that 2 is not less than 2 and displays the message :

No Errors

3. When one or more entries are blank , the COUNTIF function will return an array of values {0;1} or {1;0} or {0;0} , where the 0 signifies that the entry is blank.

When we take the reciprocal of this by using :

1/COUNTIF(Tbl_Product[Product Name],Tbl_Product[Product Name]))

we get an array :

1/{#DIV/0! ; 1} or 1/{1 ; #DIV/0!} or 1/{#DIV/0! ; #DIV/0!}

which equates to :

{#DIV/0! ; 1} or similar outputs.

Summing one or more error values results in an error value ; this is now trapped by the IFERROR function to display the message :

Check Product Names in Product Table

Narayan
 
Hi ,

The formula which is doing the validation and displaying the appropriate message in cell G22 is :

=IFERROR(IF(SUM(1/COUNTIF(Tbl_Product[Product Name],Tbl_Product[Product Name]))<COUNTA(Tbl_Product[Product Name]),"Duplicate Product Names","No Errors"),"Check Product Names in Product Table")

Suppose we consider 2 entries in the Product Name field in the table ; the possibilities are :

1. The two names are the same

2. The two names are different

3. One or both the names are blank

Firstly , the IF function is checking for the results of 2 calculations :

a. SUM(1/COUNTIF(Tbl_Product[Product Name],Tbl_Product[Product Name]))

b. COUNTA(Tbl_Product[Product Name])

The second calculation is straightforward ; it counts the non-blank entries in the field ; if both entries are blank , the result will be 0 , if one is non-blank , the result will be 1 , and if both are non-blank , the result will be 2.

This calculation is independent of whether the two entries are the same or they are different.

The first calculation is slightly more complex ; the COUNTIF function has two parameters , where the first parameter is a range , and the second parameter is usually a scalar or single value ; the COUNTIF function returns the number of occurrences of this single value in the range that is the first parameter.

However , the COUNTIF function can also accept a range as the second parameter , and in this case , the return value is an array which consists of the same number of elements as the second parameter. Each element of the output array gives the number of occurrences of each element of the second parameter in the first parameter.

To explain this more clearly , consider two ranges A33:A42 and B25:B29.

A33:A42 has the values 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10

B25:B29 has the values 3 , 7 , 4 , 3 , 4

The following formula :

=COUNTIF(A33:A42,B25:B29) will return the output array of values :

{1;1;1;1;1}

since each of the elements of the second parameter occurs once in the first parameter. There are 5 elements in the output array because the second parameter has 5 elements in the range B25:B29.

The following formula :

=COUNTIF(B25:B29,A33:A42) will return the output array of values :

{0;0;2;2;0;0;1;0;0;0}


since 1 and 2 do not occur in the first parameter , 3 and 4 each occurs twice , 5 and 6 do not occur , 7 occurs once , while 8 and 9 too do not occur in the first parameter. There are 10 elements in the output array because the second parameter has 10 elements in the range A33:A42.

Now that we have understood this , let us consider the three cases that we listed earlier :

1. When the two names are the same , the COUNTIF function will return an array of values {2;2} since each name occurs twice , because the first parameter and the second parameter are the same range.

When we take the reciprocal of this by using :

1/COUNTIF(Tbl_Product[Product Name],Tbl_Product[Product Name]))

we get an array :

1/{2 ; 2}

which equates to :

{0.5 ; 0.5}

Now you can understand why the SUM function is necessary as a wrapper around this ; we need a SUM function to add together all the elements of the result array ; in this case adding 0.5 and 0.5 gives us 1.

The IF statement now checks and finds that 1 is less than 2 and displays the error message :

Duplicate Product Names

2. When the two names are different , the COUNTIF function will return an array of values {1;1} since each name occurs only once , because the first parameter and the second parameter are the same range.

When we take the reciprocal of this by using :

1/COUNTIF(Tbl_Product[Product Name],Tbl_Product[Product Name]))

we get an array :

1/{1 ; 1}

which equates to :

{1 ; 1}

In this case adding 1 and 1 gives us 2.

The IF statement now checks and finds that 2 is not less than 2 and displays the message :

No Errors

3. When one or more entries are blank , the COUNTIF function will return an array of values {0;1} or {1;0} or {0;0} , where the 0 signifies that the entry is blank.

When we take the reciprocal of this by using :

1/COUNTIF(Tbl_Product[Product Name],Tbl_Product[Product Name]))

we get an array :

1/{#DIV/0! ; 1} or 1/{1 ; #DIV/0!} or 1/{#DIV/0! ; #DIV/0!}

which equates to :

{#DIV/0! ; 1} or similar outputs.

Summing one or more error values results in an error value ; this is now trapped by the IFERROR function to display the message :

Check Product Names in Product Table

Narayan

Thanks, That makes sense now
 
Back
Top