This is a migrated thread and some comments may be shown as answers.
IfError() and Match()
4 Answers 25 Views
This is a migrated thread and some comments may be shown as answers.
MikeWiese
Top achievements
Rank 1
MikeWiese asked on 17 Mar 2017, 04:03 AM

The IfError and Match functions are not implemented. Any chance of getting these implemented?

 

Also Vlookup, but this has already been mentioned on another thread

4 Answers, 1 is accepted

Sort by
0
Tanya
Telerik team
answered on 21 Mar 2017, 04:41 PM
Hello Mike,

The tasks scheduled for each release depend on their priority in our backlog, which is calculated depending on many factors, the most important of which is the client demand.

The three functions are currently not scheduled and I am unable to give you a time frame when they will be available. I can shed some light on their current priority. VLOOKUP and IFERROR are among the functions with the highest priority, while MATCH is with lower priority - I just created a feedback item for it. I can see that you have already found several items but here are the links to the feature requests for the discussed functions:

  - IFERROR function
  - MATCH function
  - VLOOKUP function

Although the functions are not built-in, you can implement them as custom functions and use them in your application. The public feedback items contain links to the resources you can use as a reference for the implementation.

Hope this is helpful.

Regards,
Tanya
Telerik by Progress

0
Guy
Top achievements
Rank 2
answered on 24 Oct 2017, 10:15 AM

Hi

I am using the below class and it appears to work alright. Maybe someone at Telerik can correct any errors.

 

public class TelerikSpreadsheetIfError : FunctionWithArguments
    {
        public static readonly string FunctionName = "IFERROR";
        private static readonly FunctionInfo Info;
 
        public override string Name
        {
            get
            {
                return FunctionName;
            }
        }
 
        public override FunctionInfo FunctionInfo
        {
            get
            {
                return Info;
            }
        }
 
        static TelerikSpreadsheetIfError()
        {
            string description = "Excel compatible function. Returns value_if_error if the expression is an error and the value of the expression itself otherwise.";
 
            IEnumerable<ArgumentInfo> requiredArguments = new ArgumentInfo[]
            {
                new ArgumentInfo("value", "Any value or expression or reference", ArgumentType.Any),
                new ArgumentInfo("value_if_error", "The value to use if the first argument is an error.", ArgumentType.Any),
            };
 
            Info = new FunctionInfo(FunctionName, FunctionCategory.Logical, description, requiredArguments);
        }
 
        protected override RadExpression EvaluateOverride(FunctionEvaluationContext<object> context)
        {
            try
            {
                object firstArg = context.Arguments[0];
                object secondArg = context.Arguments[1];
                 
                if ( firstArg is ErrorExpression )
                {
                    return secondArg as RadExpression;
                }
                else
                {
                    return firstArg as RadExpression;
                }
            }
            catch (System.Exception ex)
            {               
                return ErrorExpressions.NullError;
            }
        }
    }
0
Guy
Top achievements
Rank 2
answered on 24 Oct 2017, 01:02 PM

Ok, that evaluation function is not going to work in all cases. Try this instead:

 

protected override RadExpression EvaluateOverride(FunctionEvaluationContext<object> context)
        {
            try
            {
                object firstArg = context.Arguments[0];
                object secondArg = context.Arguments[1];
 
                var firstArgConst = ((RadExpression)context.Arguments[0]).GetValueAsNonArrayConstantExpression(false);
                 
                if (firstArgConst is ErrorExpression )
                {
                    return secondArg as RadExpression;
                }
                else
                {
                    return firstArg as RadExpression;
                }
            }
            catch (System.Exception ex)
            {               
                return ErrorExpressions.NullError;
            }
        }
0
Anna
Telerik team
answered on 26 Oct 2017, 10:49 AM
Hi Guy,

I tested your code with all possible errors and scenarios that I could think of and that I found in the xlsx specification. Looks good! Thank you for providing it in the forum where others might make use of it as well.

Regards,
Anna
Progress Telerik

Asked by
MikeWiese
Top achievements
Rank 1
Answers by
Tanya
Telerik team
Guy
Top achievements
Rank 2
Anna
Telerik team
Share this question
or