Custom Function in Array Formula

  • Thread starter Thread starter RincewindWIZZ
  • Start date Start date
R

RincewindWIZZ

Sorry if this has been asked before but I failed to find it.
I want to write a small function for use in an array function.
The function (MinChar) returns the smallest lexicographically of its
parameters (like MIN but works with text). I want to use this function
in an array formula
={MinChar(IF(E10:E14>B8,E10:E14))}
so that I get the smallest value that is bigger than B8.

={MIN(IF(E10:E14>B8,E10:E14))} where the values are all numeric works
just fine so why does the custom function not work?

I have tried parameters as a range or as a parameter array but I get
#VALUE every time.

MinChar(E10:E14) works fine.

TFAI
 
RincewindWIZZ said:
={MIN(IF(E10:E14>B8,E10:E14))} where the values are all numeric works
just fine so why does the custom function not work?
Click to expand...
Have you tried =IF(MinChar(E10:E14)>B8,MinChar(E10:E14)) ?

I noticed that =IF(E10>B8,E10) works with letters just fine, but if you
replace E10 with the array E10:E14 it throws an error. So I am thinking
that it works sort of like the MINA function which has the following
caveat.

If an argument is an array or reference, only values in that array or
reference are used. Empty cells and text values in the array or
reference are ignored.
 
Back
Top