public void Function(IFunctionArgs args, IExcelCaller caller)
{
// Syntax: LN(number)
// https://support.office.com/en-gb/article/LN-function-81fe1ed7-dac9-4acd-ba1d-07a142c6118f
if (args.Parameters.Length != 1)
{
throw new ArgumentException(
$"LN function takes only 1 argument, got {args.Parameters.Length}");
}
var arg = args.Parameters[0].Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
var val = Conversion.ToDoubleOrErrorValue(arg);
if (val is ErrorValue)
{
args.Result = val;
return;
}
args.Result = Math.Log((double)val);
}
public void Function(IFunctionArgs args, IExcelCaller caller)
{
// Syntax: RADIANS(angle)
// https://support.office.com/en-gb/article/RADIANS-function-ac409508-3d48-45f5-ac02-1497c92de5bf
if (args.Parameters.Length != 1)
{
throw new ArgumentException(
$"RADIANS function takes only 1 argument, got {args.Parameters.Length}");
}
var arg = args.Parameters[0].Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
var val = Conversion.ToDoubleOrErrorValue(arg);
if (val is ErrorValue)
{
args.Result = val;
return;
}
var angle = (double)val;
args.Result = Math.PI / 180 * angle;
}
public void Function(IFunctionArgs args, IExcelCaller caller)
{
// Syntax: SQRT(number)
// https://support.office.com/en-gb/article/SQRT-function-654975c2-05c4-4831-9a24-2c65e4040fdf
if (args.Parameters.Length != 1)
{
throw new ArgumentException(
$"SQRT function takes only 1 argument, got {args.Parameters.Length}");
}
var arg = args.Parameters[0].Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
var val = Conversion.ToDoubleOrErrorValue(arg);
if (val is ErrorValue)
{
args.Result = val;
return;
}
args.Result = Conversion.ErrorValueOnInvalidDouble(Math.Sqrt((double)val));
}
public void Function(IFunctionArgs args, IExcelCaller caller)
{
// Syntax: SUM(number1,[number2],...)
// https://support.office.com/en-GB/article/SUM-function-043e1c7d-7726-4e80-8f32-07b23e057f89
if (args.Parameters.Length < 1)
{
throw new ArgumentException(
$"SUM function takes at least 1 arguments, got {args.Parameters.Length}",
nameof(args));
}
var numbers = new List <double>();
foreach (var argsParameter in args.Parameters)
{
var arg = argsParameter.Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
var val = Conversion.ToDoubleOrErrorValue(arg);
if (val is ErrorValue)
{
args.Result = val;
return;
}
numbers.Add((double)val);
}
args.Result = numbers.Sum();
}
private static void IfErrorFunction(IFunctionArgs args, IExcelCaller caller)
{
if (args.Parameters.Length != 2)
{
throw new ArgumentException(
"IFERROR function takes exactly 2 arguments",
nameof(args));
}
try
{
var arg = args.Parameters[0].Evaluate();
if (arg is ErrorValue)
{
args.Result = args.Parameters[1].Evaluate();
return;
}
args.Result = arg;
}
catch (Exception)
{
args.Result = args.Parameters[1].Evaluate();
}
}
private static void IfFunction(IFunctionArgs args, IExcelCaller caller)
{
if (args.Parameters.Length != 2 && args.Parameters.Length != 3)
{
throw new ArgumentException(
"IF function takes 2 or 3 arguments",
nameof(args));
}
var arg = args.Parameters[0].Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
if (args.Parameters.Length == 3)
{
args.Result = Convert.ToBoolean(arg)
? args.Parameters[1].Evaluate()
: args.Parameters[2].Evaluate();
}
else
{
args.Result = Convert.ToBoolean(arg)
? args.Parameters[1].Evaluate()
: false;
}
}
public void Function(IFunctionArgs args, IExcelCaller caller)
{
// Syntax: AVERAGE(number1, [number2], ...)
// https://support.office.com/en-GB/article/AVERAGE-function-047bac88-d466-426c-a32b-8f33eb960cf6
if (args.Parameters.Length < 1)
{
throw new ArgumentException(
$"AVERAGE function takes at least 1 arguments, got {args.Parameters.Length}",
nameof(args));
}
var numbers = new List <double>();
foreach (var argsParameter in args.Parameters)
{
var arg = argsParameter.Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
var val = Conversion.ToDoubleOrErrorValue(arg);
if (val is ErrorValue)
{
args.Result = val;
return;
}
numbers.Add((double)val);
}
args.Result = numbers.Average();
}
public void Function(IFunctionArgs args, IExcelCaller caller)
{
// Syntax: MIN(number1, [number2], ...)
// https://support.office.com/en-GB/article/MIN-function-61635d12-920f-4ce2-a70f-96f202dcc152
if (args.Parameters.Length < 1)
{
throw new ArgumentException(
$"MIN function takes at least 1 arguments, got {args.Parameters.Length}",
nameof(args));
}
var numbers = new List <double>();
foreach (var argsParameter in args.Parameters)
{
var arg = argsParameter.Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
var val = Conversion.ToDoubleOrErrorValue(arg);
if (val is ErrorValue)
{
args.Result = val;
return;
}
numbers.Add((double)val);
}
args.Result = numbers.Min();
}
public void Function(IFunctionArgs args, IExcelCaller caller)
{
// Syntax: IF(logical_test, value_if_true, [value_if_false])
// https://support.office.com/en-gb/article/IF-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2
if (args.Parameters.Length != 2 && args.Parameters.Length != 3)
{
throw new ArgumentException(
"IF function takes 2 or 3 arguments",
nameof(args));
}
var arg = args.Parameters[0].Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
if (args.Parameters.Length == 3)
{
args.Result = Convert.ToBoolean(arg)
? args.Parameters[1].Evaluate()
: args.Parameters[2].Evaluate();
}
else
{
args.Result = Convert.ToBoolean(arg)
? args.Parameters[1].Evaluate()
: false;
}
}
public void Function(IFunctionArgs args, IExcelCaller caller)
{
// Syntax: IFERROR(value, value_if_error)
// https://support.office.com/en-gb/article/IFERROR-function-c526fd07-caeb-47b8-8bb6-63f3e417f611
if (args.Parameters.Length != 2)
{
throw new ArgumentException(
"IFERROR function takes exactly 2 arguments",
nameof(args));
}
try
{
var arg = args.Parameters[0].Evaluate();
if (arg is ErrorValue)
{
args.Result = args.Parameters[1].Evaluate();
return;
}
args.Result = arg;
}
catch (Exception)
{
args.Result = args.Parameters[1].Evaluate();
}
}
public void Function(IFunctionArgs args, IExcelCaller caller)
{
// Syntax: COUNT(value1, [value2], ...)
// https://support.office.com/en-gb/article/COUNT-function-a59cd7fc-b623-4d93-87a4-d23bf411294c
if (args.Parameters.Length < 1)
{
throw new ArgumentException(
$"COUNT function takes at least 1 arguments, got {args.Parameters.Length}",
nameof(args));
}
var numbers = new List <double>();
foreach (var argsParameter in args.Parameters)
{
var arg = argsParameter.Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
var val = Conversion.ToDoubleOrErrorValue(arg);
if (val is ErrorValue)
{
args.Result = val;
return;
}
numbers.Add((double)val);
}
args.Result = (double)numbers.Count;
}
public void Function(IFunctionArgs args, IExcelCaller caller)
{
// Syntax: OR(logical1, [logical2], ...)
// https://support.office.com/en-gb/article/OR-function-7d17ad14-8700-4281-b308-00b131e22af0
var result = false;
foreach (var fx in args.Parameters)
{
var arg = fx.Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
if (!(arg is bool))
{
args.Result = ErrorValue.Num;
return;
}
if (!(bool)arg)
{
continue;
}
result = true;
break;
}
args.Result = result;
}
public void Function(IFunctionArgs args, IExcelCaller caller)
{
// Syntax: MAX(number1, [number2], ...)
// https://support.office.com/en-GB/article/MAX-function-e0012414-9ac8-4b34-9a47-73e662c08098
if (args.Parameters.Length < 1)
{
throw new ArgumentException(
$"MAX function takes at least 1 arguments, got {args.Parameters.Length}",
nameof(args));
}
var numbers = new List <double>();
foreach (var argsParameter in args.Parameters)
{
var arg = argsParameter.Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
var val = Conversion.ToDoubleOrErrorValue(arg);
if (val is ErrorValue)
{
args.Result = val;
return;
}
numbers.Add((double)val);
}
args.Result = numbers.Max();
}
private static void AddThemFunction(IFunctionArgs args, IExcelCaller caller)
{
var arg1 = StringConversion.ToDouble(args.Parameters[0].Evaluate());
var arg2 = StringConversion.ToDouble(args.Parameters[1].Evaluate());
args.Result = arg1 + arg2;
}
private static void LnFunction(IFunctionArgs args, IExcelCaller caller)
{
if (args.Parameters.Length != 1)
{
throw new ArgumentException(
$"LN function takes only 1 argument, got {args.Parameters.Length}");
}
var arg = args.Parameters[0].Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
var val = Conversion.ToDoubleOrErrorValue(arg);
if (val is ErrorValue)
{
args.Result = val;
return;
}
args.Result = Math.Log((double)val);
}
private static void MinFunction(IFunctionArgs args, IExcelCaller caller)
{
if (args.Parameters.Length < 1)
{
throw new ArgumentException(
$"MIN function takes at least 1 arguments, got {args.Parameters.Length}",
nameof(args));
}
var numbers = new List <double>();
foreach (var argsParameter in args.Parameters)
{
var arg = argsParameter.Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
var val = Conversion.ToDoubleOrErrorValue(arg);
if (val is ErrorValue)
{
args.Result = val;
return;
}
numbers.Add((double)val);
}
args.Result = numbers.Min();
}
private static void OrFunction(IFunctionArgs args, IExcelCaller caller)
{
var result = false;
foreach (var fx in args.Parameters)
{
var arg = fx.Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
if (!(arg is bool))
{
args.Result = ErrorValue.Num;
return;
}
if (!(bool)arg)
{
continue;
}
result = true;
break;
}
args.Result = result;
}
private static void PiFunction(IFunctionArgs args, IExcelCaller caller)
{
if (args.Parameters.Length != 0)
{
throw new ArgumentException(
$"PI function takes no arguments, got {args.Parameters.Length}",
nameof(args));
}
args.Result = Math.PI;
}
public void Function(IFunctionArgs args, IExcelCaller caller)
{
if (args.Parameters.Length != 1 && args.Parameters.Length != 2)
{
throw new ArgumentException(
$"WEIGHTED function takes 1 or 2 arguments, got {args.Parameters.Length}",
nameof(args));
}
var arg1 = args.Parameters[0].Evaluate();
if (arg1 is ErrorValue)
{
args.Result = arg1;
return;
}
var arg2 = new object();
if (args.Parameters.Length == 2)
{
arg2 = args.Parameters[1].Evaluate();
if (arg2 is ErrorValue)
{
args.Result = arg2;
return;
}
}
else
{
arg2 = 1;
}
var val1 = Conversion.ToDoubleOrErrorValue(arg1);
if (val1 is ErrorValue)
{
args.Result = val1;
return;
}
var val2 = Conversion.ToDoubleOrErrorValue(arg2);
if (val2 is ErrorValue)
{
args.Result = val2;
return;
}
args.Result = (double)val1 * (double)val2;
}
public void Function(IFunctionArgs args, IExcelCaller caller)
{
// Syntax: PI()
// https://support.office.com/en-gb/article/PI-function-264199d0-a3ba-46b8-975a-c4a04608989b
if (args.Parameters.Length != 0)
{
throw new ArgumentException(
$"PI function takes no arguments, got {args.Parameters.Length}",
nameof(args));
}
args.Result = Math.PI;
}
public void Function(IFunctionArgs args, IExcelCaller caller)
{
// Syntax: MEDIAN(number1, [number2], ...)
// https://support.office.com/en-gb/article/median-function-d0916313-4753-414c-8537-ce85bdd967d2
if (args.Parameters.Length < 1)
{
throw new ArgumentException(
$"MEDIAN function takes at least 1 arguments, got {args.Parameters.Length}",
nameof(args));
}
var numbers = new List <double>();
foreach (var argsParameter in args.Parameters)
{
var arg = argsParameter.Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
var val = Conversion.ToDoubleOrErrorValue(arg);
if (val is ErrorValue)
{
args.Result = val;
return;
}
numbers.Add((double)val);
}
double F(double[] assessments)
{
Array.Sort(assessments);
var arrayLength = assessments.Length;
if (arrayLength % 2 == 0)
{
return((assessments[(arrayLength / 2) - 1] + assessments[arrayLength / 2]) / 2);
}
return(assessments[(int)Math.Floor((double)arrayLength / 2)]);
}
args.Result = F(numbers.ToArray());
}
public void Function(IFunctionArgs args, IExcelCaller caller)
{
// Syntax: ROUNDDOWN(number, num_digits)
// https://support.office.com/en-gb/article/ROUNDDOWN-function-2ec94c73-241f-4b01-8c6f-17e6d7968f53
if (args.Parameters.Length != 2)
{
throw new ArgumentException("ROUNDDOWN function takes 2 arguments, " +
$"got {args.Parameters.Length}");
}
var arg1 = args.Parameters[0].Evaluate();
if (arg1 is ErrorValue)
{
args.Result = arg1;
return;
}
var arg2 = args.Parameters[1].Evaluate();
if (arg2 is ErrorValue)
{
args.Result = arg2;
return;
}
var val = Conversion.ToDoubleOrErrorValue(arg1);
if (val is ErrorValue)
{
args.Result = val;
return;
}
var negative = (double)val < 0;
// This code snippet based on example at http://stackoverflow.com/a/13483008
var @decimal = new decimal(Math.Abs((double)val));
var places = Convert.ToInt32(arg2);
var factor = Round.RoundFactor(places);
@decimal *= factor;
@decimal = Math.Floor(@decimal);
@decimal /= factor;
args.Result = Convert.ToDouble(negative ? -@decimal : @decimal);
}
public void Function(IFunctionArgs args, IExcelCaller caller)
{
// Syntax: POWER(number, power)
// https://support.office.com/en-gb/article/POWER-function-d3f2908b-56f4-4c3f-895a-07fb519c362a
if (args.Parameters.Length != 2)
{
throw new ArgumentException(
$"POWER function takes 2 arguments, got {args.Parameters.Length}",
nameof(args));
}
var arg1 = args.Parameters[0].Evaluate();
if (arg1 is ErrorValue)
{
args.Result = arg1;
return;
}
var arg2 = args.Parameters[1].Evaluate();
if (arg2 is ErrorValue)
{
args.Result = arg2;
return;
}
var val1 = Conversion.ToDoubleOrErrorValue(arg1);
if (val1 is ErrorValue)
{
args.Result = val1;
return;
}
var val2 = Conversion.ToDoubleOrErrorValue(arg2);
if (val2 is ErrorValue)
{
args.Result = val2;
return;
}
args.Result = Math.Pow((double)val1, (double)val2);
}
private static void RoundDownFunction(IFunctionArgs args, IExcelCaller caller)
{
if (args.Parameters.Length != 2)
{
throw new ArgumentException("ROUNDDOWN function takes 2 arguments, " +
$"got {args.Parameters.Length}");
}
var arg1 = args.Parameters[0].Evaluate();
if (arg1 is ErrorValue)
{
args.Result = arg1;
return;
}
var arg2 = args.Parameters[1].Evaluate();
if (arg2 is ErrorValue)
{
args.Result = arg2;
return;
}
var val = Conversion.ToDoubleOrErrorValue(arg1);
if (val is ErrorValue)
{
args.Result = val;
return;
}
var negative = (double)val < 0;
// This code snippet based on example at http://stackoverflow.com/a/13483008
var @decimal = new decimal(Math.Abs((double)val));
var places = Convert.ToInt32(arg2);
var factor = RoundFactor(places);
@decimal *= factor;
@decimal = Math.Floor(@decimal);
@decimal /= factor;
args.Result = Convert.ToDouble(negative ? -@decimal : @decimal);
}
private static void RangeFunction(IFunctionArgs args, IExcelCaller caller)
{
if (args.Parameters.Length != 2)
{
throw new ArgumentException(
$"RANGE function takes 2 arguments, got {args.Parameters.Length}",
nameof(args));
}
var arg1 = args.Parameters[0].Evaluate();
if (arg1 is ErrorValue)
{
args.Result = arg1;
return;
}
var arg2 = args.Parameters[1].Evaluate();
if (arg2 is ErrorValue)
{
args.Result = arg2;
return;
}
var val1 = Conversion.ToDoubleOrErrorValue(arg1);
if (val1 is ErrorValue)
{
args.Result = val1;
return;
}
var val2 = Conversion.ToDoubleOrErrorValue(arg2);
if (val2 is ErrorValue)
{
args.Result = val2;
return;
}
args.Result = (double)val2 - (double)val1;
}
public void Function(IFunctionArgs args, IExcelCaller caller)
{
// Syntax: STDEV(number1,[number2],...)
// https://support.office.com/en-gb/article/stdev-function-51fecaaa-231e-4bbb-9230-33650a72c9b0
if (args.Parameters.Length < 1)
{
throw new ArgumentException(
$"STDEV function takes at least 1 arguments, got {args.Parameters.Length}",
nameof(args));
}
var numbers = new List <double>();
foreach (var argsParameter in args.Parameters)
{
var arg = argsParameter.Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
var val = Conversion.ToDoubleOrErrorValue(arg);
if (val is ErrorValue)
{
args.Result = val;
return;
}
numbers.Add((double)val);
}
var mean = numbers.Average();
var sum = 0.0;
foreach (var number in numbers)
{
sum += Math.Pow(number - mean, 2);
}
args.Result = Math.Sqrt(sum / (numbers.Count - 1));
}
private static void AddThemFunction(IFunctionArgs args, IExcelCaller caller)
{
var arg1 = Conversion.ToDoubleOrErrorValue(args.Parameters[0].Evaluate());
if (arg1 is ErrorValue)
{
args.Result = arg1;
return;
}
var arg2 = Conversion.ToDoubleOrErrorValue(args.Parameters[1].Evaluate());
if (arg2 is ErrorValue)
{
args.Result = arg2;
return;
}
args.Result = (double)arg1 + (double)arg2;
}
public void Function(IFunctionArgs args, IExcelCaller caller)
{
// Syntax: AND(logical1, [logical2], ...)
// https://support.office.com/en-gb/article/AND-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9
if (args.Parameters.Length < 2)
{
throw new ArgumentException(
$"AND function requires at least 2 arguments, got {args.Parameters.Length}",
nameof(args));
}
var result = true;
foreach (var fx in args.Parameters)
{
var arg = fx.Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
if (!(arg is bool))
{
args.Result = ErrorValue.Num;
return;
}
if ((bool)arg)
{
continue;
}
result = false;
break;
}
args.Result = result;
}
private static void StdevFunction(IFunctionArgs args, IExcelCaller caller)
{
if (args.Parameters.Length < 1)
{
throw new ArgumentException(
$"STDEV function takes at least 1 arguments, got {args.Parameters.Length}",
nameof(args));
}
var numbers = new List <double>();
foreach (var argsParameter in args.Parameters)
{
var arg = argsParameter.Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
var val = Conversion.ToDoubleOrErrorValue(arg);
if (val is ErrorValue)
{
args.Result = val;
return;
}
numbers.Add((double)val);
}
var mean = numbers.Average();
var sum = 0.0;
foreach (var number in numbers)
{
sum += Math.Pow(number - mean, 2);
}
args.Result = Math.Sqrt(sum / (numbers.Count - 1));
}
private static void AndFunction(IFunctionArgs args, IExcelCaller caller)
{
if (args.Parameters.Length < 2)
{
throw new ArgumentException(
$"AND function requires at least 2 arguments, got {args.Parameters.Length}",
nameof(args));
}
var result = true;
foreach (var fx in args.Parameters)
{
var arg = fx.Evaluate();
if (arg is ErrorValue)
{
args.Result = arg;
return;
}
if (!(arg is bool))
{
args.Result = ErrorValue.Num;
return;
}
if ((bool)arg)
{
continue;
}
result = false;
break;
}
args.Result = result;
}
