MINIF UDF

[kittronald]

Does anyone know how to create a MINIF UDF to find the smallest number
in an array greater than X ?

For example, =MINIF({0,1,2,3},">0") would return a value of 1.

I'm trying to create a formula that will look down the Name column and
determine if there are any duplicate values with common root characters in
the corresponding Code column. The Name's Code with the fewest characters
will be the active code and the longer Codes will be classified as inactive.
Classifying a Name's Code as inactive means it will be ignored in other
calculations.



A B C

1 Code Name Status

2 ABC Apple

3 BCD Banana

4 ABCD Apple



Using the logic below, I'm looking to see if there is more than one Name
equal to Apple and if so, which of the Codes are to be considered active. In
the C column, a formula is used to determine the active/inactive status of
that row's Code.

In C2, I want to create an array with SUMPRODUCT that:

1) Looks for duplicate names in the Name column

B2:B4=B2 would create an array of {1,0,1}

2) Use LEN to determine the number of characters for that row's Code

LEN(A2:A4) would create an array {3,3,4}

3) Multiply these two arrays with a result of {3,0,4}

4) Use MINIF({3,0,4},">0") to get a result of 3

5) Compare the MINIF result with the LEN of that row's Code. If the
values are equal, the Code for that Name's row is active. Otherwise, it is
inactive.

IF(MINIF({3,0,4},">0")=LEN(A2),"Active","Inactive")



So the sticking point is coming up with a MINIF UDF.

Any ideas ?



- Ronald K.

 

[Ron Rosenfeld]

Does anyone know how to create a MINIF UDF to find the smallest number
in an array greater than X ?

For example, =MINIF({0,1,2,3},">0") would return a value of 1.

I'm trying to create a formula that will look down the Name column and
determine if there are any duplicate values with common root characters in
the corresponding Code column. The Name's Code with the fewest characters
will be the active code and the longer Codes will be classified as inactive.
Classifying a Name's Code as inactive means it will be ignored in other
calculations.



A B C

1 Code Name Status

2 ABC Apple

3 BCD Banana

4 ABCD Apple



Using the logic below, I'm looking to see if there is more than one Name
equal to Apple and if so, which of the Codes are to be considered active. In
the C column, a formula is used to determine the active/inactive status of
that row's Code.

In C2, I want to create an array with SUMPRODUCT that:

1) Looks for duplicate names in the Name column

B2:B4=B2 would create an array of {1,0,1}

2) Use LEN to determine the number of characters for that row's Code

LEN(A2:A4) would create an array {3,3,4}

3) Multiply these two arrays with a result of {3,0,4}

4) Use MINIF({3,0,4},">0") to get a result of 3

5) Compare the MINIF result with the LEN of that row's Code. If the
values are equal, the Code for that Name's row is active. Otherwise, it is
inactive.

IF(MINIF({3,0,4},">0")=LEN(A2),"Active","Inactive")



So the sticking point is coming up with a MINIF UDF.

Any ideas ?



- Ronald K.

Well, you can do it all with a formula using normal worksheet functions:

This formula must be **array-entered**:

=IF(INDEX(Codes,MATCH(MIN(IF(LEN(Codes)*(B2=Names)>0,
LEN(Codes)*(B2=Names))),LEN(Codes)*(B2=Names),0))=A2,"Active","Inactive")

 

[Martin Brown]

Does anyone know how to create a MINIF UDF to find the smallest number
in an array greater than X ?

For example, =MINIF({0,1,2,3},">0") would return a value of 1.

Something along the lines of =MIN(IF(C1:C999>0, C1:C999,1E+99))
entered as an array formula ought to do it.

I'm trying to create a formula that will look down the Name column and
determine if there are any duplicate values with common root characters in
the corresponding Code column. The Name's Code with the fewest characters
will be the active code and the longer Codes will be classified as inactive.
Classifying a Name's Code as inactive means it will be ignored in other
calculations.



A B C

1 Code Name Status

2 ABC Apple

3 BCD Banana

4 ABCD Apple



Using the logic below, I'm looking to see if there is more than one Name
equal to Apple and if so, which of the Codes are to be considered active. In
the C column, a formula is used to determine the active/inactive status of
that row's Code.

In C2, I want to create an array with SUMPRODUCT that:

1) Looks for duplicate names in the Name column

B2:B4=B2 would create an array of {1,0,1}

2) Use LEN to determine the number of characters for that row's Code

LEN(A2:A4) would create an array {3,3,4}

3) Multiply these two arrays with a result of {3,0,4}

4) Use MINIF({3,0,4},">0") to get a result of 3

5) Compare the MINIF result with the LEN of that row's Code. If the
values are equal, the Code for that Name's row is active. Otherwise, it is
inactive.

IF(MINIF({3,0,4},">0")=LEN(A2),"Active","Inactive")



So the sticking point is coming up with a MINIF UDF.

Any ideas ?

That will run *so* incredibly slowly that it won't be worth doing.

Consider sorting the data by Name and then by Code and applying your
logic sequentially to any adjacent rows with duplicate names. If I have
read it right the active ones will always have a different name to the
line above after sorting. If necessary to be able to restore to original
order add a dummy tag based on row number.

Regards,
Martin Brown

 

[kittronald]

Ron and Martin,

Thank you very much for the in-depth responses.

Ron's formula returns the right result. The special thing about the
formula is that it uses the IF trick to maintain a FALSE value rather than
returning zeros in the arrays. This is why I thought about a MINIF
function - to get around the zeros which would always be the result of using
MIN. Someone had previously helped me with another formula a while back
using this method, but due to my limited experience with it, I didn't think
to use it.

Martin is right about the array entered formula executing slowly though.
With about 6,500 cells, it took 96.91491 seconds to calculate.

However, I made a couple changes:

=IF(COUNTIF(Names,B2)=1,"",IF(INDEX(Codes,SUMPRODUCT
(MATCH(MIN(IF(LEN(Codes)*(B2=Names)>0,LEN(Codes)*(B2=Names))),
LEN(Codes)*(B2=Names),0)))=A2,"Active","Inactive"))

To avoid future edits that would break the formula when I wouldn't
remember this is array entered , I converted it to a non-array entered
formula using SUMPRODUCT.

The formulas now take 7.26163 seconds to calculate.

Thank you both for the help. I've been trying to get this formula to work
without success for a couple months.

And now it's over !




- Ronald K.

 

[Ron Rosenfeld]

Ron and Martin,

Thank you very much for the in-depth responses.

Ron's formula returns the right result. The special thing about the
formula is that it uses the IF trick to maintain a FALSE value rather than
returning zeros in the arrays. This is why I thought about a MINIF
function - to get around the zeros which would always be the result of using
MIN. Someone had previously helped me with another formula a while back
using this method, but due to my limited experience with it, I didn't think
to use it.

Martin is right about the array entered formula executing slowly though.
With about 6,500 cells, it took 96.91491 seconds to calculate.

However, I made a couple changes:

=IF(COUNTIF(Names,B2)=1,"",IF(INDEX(Codes,SUMPRODUCT
(MATCH(MIN(IF(LEN(Codes)*(B2=Names)>0,LEN(Codes)*(B2=Names))),
LEN(Codes)*(B2=Names),0)))=A2,"Active","Inactive"))

To avoid future edits that would break the formula when I wouldn't
remember this is array entered , I converted it to a non-array entered
formula using SUMPRODUCT.

The formulas now take 7.26163 seconds to calculate.

Thank you both for the help. I've been trying to get this formula to work
without success for a couple months.

And now it's over !

Glad to help. Thanks for the feedback.