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.

On Wed, 3 Aug 2011 02:36:35 -0400, "kittronald" wrote:

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")
----------------------------------------
To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

On 03/08/2011 07:36, kittronald wrote:
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:C9990, 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

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,SUMPRODU CT
(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.

On Thu, 4 Aug 2011 05:41:07 -0400, "kittronald" wrote:

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,SUMPRODU CT
(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.