def count(*args): # Excel reference: https://support.office.com/en-us/article/COUNT-function-a59cd7fc-b623-4d93-87a4-d23bf411294c
l = list(args)
total = 0
for arg in l:
if type(arg) == list:
total += len(filter(lambda x: is_number(x) and type(x) is not bool, arg)) # count inside a list
elif is_number(arg): # int() is used for text representation of numbers
total += 1
return total
def roundup_unwrapped(number, num_digits):
# Excel reference: https://support.office.com/en-us/article/
# ROUNDUP-function-F8BC9B23-E795-47DB-8703-DB171D0C42A7
number, num_digits = coerce_to_number(number), coerce_to_number(num_digits)
if not is_number(number) or not is_number(num_digits):
return VALUE_ERROR
if isinstance(number, bool):
number = int(number)
quant = Decimal('1E{}{}'.format('+-'[num_digits >= 0], abs(num_digits)))
return float(Decimal(repr(number)).quantize(quant, rounding=ROUND_UP))
def xround(number, num_digits = 0): # Excel reference: https://support.office.com/en-us/article/ROUND-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c
if not is_number(number):
raise TypeError("%s is not a number" % str(number))
if not is_number(num_digits):
raise TypeError("%s is not a number" % str(num_digits))
if num_digits >= 0: # round to the right side of the point
return float(Decimal(repr(number)).quantize(Decimal(repr(pow(10, -num_digits))), rounding=ROUND_HALF_UP))
# see https://docs.python.org/2/library/functions.html#round
# and https://gist.github.com/ejamesc/cedc886c5f36e2d075c5
else:
return round(number, num_digits)
def count(*args):
# Excel reference: https://support.office.com/en-us/article/
# COUNT-function-a59cd7fc-b623-4d93-87a4-d23bf411294c
total = 0
for arg in args:
if isinstance(arg, list):
# count inside a list
total += len(
[x for x in arg if is_number(x) and not isinstance(x, bool)])
else:
total += int(is_number(arg))
return total
def count(*args):
# Excel reference: https://support.office.com/en-us/article/
# COUNT-function-a59cd7fc-b623-4d93-87a4-d23bf411294c
total = 0
for arg in args:
if isinstance(arg, list):
# count inside a list
total += len(
[x for x in arg if is_number(x) and not isinstance(x, bool)])
else:
total += int(is_number(arg))
return total
def type_convert_float(value):
if is_number(value):
value = float(value)
else:
value = None
return value
def npv(rate, *args):
# Excel reference: https://support.office.com/en-us/article/
# NPV-function-8672CB67-2576-4D07-B67B-AC28ACF2A568
rate += 1
cashflow = [x for x in flatten(args, coerce=coerce_to_number)
if is_number(x) and not isinstance(x, bool)]
return sum(x * rate ** -i for i, x in enumerate(cashflow, start=1))
def mod(number, divisor):
# Excel reference: https://support.office.com/en-us/article/
# MOD-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3
if number in ERROR_CODES:
return number
if divisor in ERROR_CODES:
return divisor
number, divisor = coerce_to_number(number), coerce_to_number(divisor)
if divisor in (0, None):
return DIV0
if not is_number(number) or not is_number(divisor):
return VALUE_ERROR
return number % divisor
def count(
*args
): # Excel reference: https://support.office.com/en-us/article/COUNT-function-a59cd7fc-b623-4d93-87a4-d23bf411294c
l = list(args)
total = 0
for arg in l:
if type(arg) == list:
total += len([
x for x in arg if is_number(x) and type(x) is not bool
]) # count inside a list
elif is_number(
arg): # int() is used for text representation of numbers
total += 1
return total
def is_leap_year(year):
if not is_number(year):
raise TypeError("%s must be a number" % str(year))
if year <= 0:
raise TypeError("%s must be strictly positive" % str(year))
# Watch out, 1900 is a leap according to Excel =>
# https://support.microsoft.com/en-us/kb/214326
return year % 4 == 0 and year % 100 != 0 or year % 400 == 0 or year == 1900
def xround(
number,
num_digits=0
): # Excel reference: https://support.office.com/en-us/article/ROUND-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c
if not is_number(number):
raise TypeError("%s is not a number" % str(number))
if not is_number(num_digits):
raise TypeError("%s is not a number" % str(num_digits))
if num_digits >= 0: # round to the right side of the point
return float(
Decimal(repr(number)).quantize(Decimal(repr(pow(10, -num_digits))),
rounding=ROUND_HALF_UP))
# see https://docs.python.org/2/library/functions.html#round
# and https://gist.github.com/ejamesc/cedc886c5f36e2d075c5
else:
return round(number, num_digits)
def xl_round(number, num_digits=0):
"""
The ROUND function rounds a number to a specified number of digits. For example,
if cell A1 contains 23.7825, and you want to round that value to two decimal places,
you can use the following formula:
=ROUND(A1, 2)
The result of this function is 23.78.
:param number: Required. The number that you want to round.
:param num_digits: Required. The number of digits to which you want to round the number argument.
:return: the rounded number
.. remarks::
* If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places.
* If num_digits is 0, the number is rounded to the nearest integer.
* If num_digits is less than 0, the number is rounded to the left of the decimal point.
* To always round up (away from zero), use the ROUNDUP function.
* To always round down (toward zero), use the ROUNDDOWN function.
* To round a number to a specific multiple (for example, to round to the nearest 0.5), use the MROUND function.
.. reference::
https://support.office.com/en-us/article/ROUND-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c
"""
if not is_number(number):
raise TypeError("%s is not a number" % str(number))
if not is_number(num_digits):
raise TypeError("%s is not a number" % str(num_digits))
# round to the right side of the point
if num_digits >= 0:
return float(Decimal(repr(number)).quantize(Decimal(repr(pow(10, -num_digits))), rounding=ROUND_HALF_UP))
# see https://docs.python.org/2/library/functions.html#round
# and https://gist.github.com/ejamesc/cedc886c5f36e2d075c5
else:
return round(number, num_digits)
def mid(text, start_num, num_chars):
# Excel reference: https://support.office.com/en-us/article/
# MID-MIDB-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028
if text in ERROR_CODES:
return text
if start_num in ERROR_CODES:
return start_num
if num_chars in ERROR_CODES:
return num_chars
start_num = coerce_to_number(start_num)
num_chars = coerce_to_number(num_chars)
if not is_number(start_num) or not is_number(num_chars):
return VALUE_ERROR
if start_num < 1 or num_chars < 0:
return VALUE_ERROR
start_num = int(start_num) - 1
return str(text)[start_num:start_num + int(num_chars)]
def xround(number, num_digits=0):
# Excel reference: https://support.office.com/en-us/article/
# ROUND-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c
if number in ERROR_CODES:
return number
if num_digits in ERROR_CODES:
return num_digits
number, num_digits = coerce_to_number(number), coerce_to_number(num_digits)
if not is_number(number) or not is_number(num_digits):
return VALUE_ERROR
num_digits = int(num_digits)
if num_digits >= 0: # round to the right side of the point
return float(
Decimal(repr(number)).quantize(Decimal(repr(pow(10, -num_digits))),
rounding=ROUND_HALF_UP))
# see https://docs.python.org/2/library/functions.html#round
# and https://gist.github.com/ejamesc/cedc886c5f36e2d075c5
else:
return round(number, num_digits)
def count(*args):
"""
:param args:
:return:
.. reference::
https://support.office.com/en-us/article/COUNT-function-a59cd7fc-b623-4d93-87a4-d23bf411294c
"""
l = list(args)
total = 0
for arg in l:
if isinstance(arg, list_types):
# count inside a list
total += len(list(filter(lambda x: is_number(x) and type(x) is not bool, arg)))
# int() is used for text representation of numbers
elif is_number(arg):
total += 1
return total
def right(text, num_chars=1):
# Excel reference: https://support.office.com/en-us/article/
# RIGHT-RIGHTB-functions-240267EE-9AFA-4639-A02B-F19E1786CF2F
if text in ERROR_CODES:
return text
if num_chars in ERROR_CODES:
return num_chars
num_chars = coerce_to_number(num_chars)
if not is_number(num_chars) or num_chars < 0:
return VALUE_ERROR
if num_chars == 0:
return ''
else:
return str(text)[-int(num_chars):]
def wrapper(*args):
new_args = tuple(coerce_to_number(a, convert_all=True)
if i in param_indices else a
for i, a in enumerate(args))
error = next((a for i, a in enumerate(new_args)
if i in param_indices and a in ERROR_CODES), None)
if error:
return error
if any(i in param_indices and not is_number(a)
for i, a in enumerate(new_args)):
return VALUE_ERROR
try:
return f(*new_args)
except ValueError as exc:
if "math domain error" in str(exc):
return NUM_ERROR
raise # pragma: no cover
def wrapper(*args):
new_args = tuple(coerce_to_number(a, convert_all=True)
if i in param_indices else a
for i, a in enumerate(args))
error = next((a for i, a in enumerate(new_args)
if i in param_indices and a in ERROR_CODES), None)
if error:
return error
if any(i in param_indices and not is_number(a)
for i, a in enumerate(new_args)):
return VALUE_ERROR
try:
return f(*new_args)
except ValueError as exc:
if "math domain error" in str(exc):
return NUM_ERROR
raise # pragma: no cover
def yearfrac(start_date, end_date, basis = 0): # Excel reference: https://support.office.com/en-us/article/YEARFRAC-function-3844141e-c76d-4143-82b6-208454ddc6a8
def actual_nb_days_ISDA(start, end): # needed to separate days_in_leap_year from days_not_leap_year
y1, m1, d1 = start
y2, m2, d2 = end
days_in_leap_year = 0
days_not_in_leap_year = 0
year_range = range(y1, y2 + 1)
for y in year_range:
if y == y1 and y == y2:
nb_days = date(y2, m2, d2) - date(y1, m1, d1)
elif y == y1:
nb_days = date(y1 + 1, 1, 1) - date(y1, m1, d1)
elif y == y2:
nb_days = date(y2, m2, d2) - date(y2, 1, 1)
else:
nb_days = 366 if is_leap_year(y) else 365
if is_leap_year(y):
days_in_leap_year += nb_days
else:
days_not_in_leap_year += nb_days
return (days_not_in_leap_year, days_in_leap_year)
def actual_nb_days_AFB_alter(start, end): # http://svn.finmath.net/finmath%20lib/trunk/src/main/java/net/finmath/time/daycount/DayCountConvention_ACT_ACT_YEARFRAC.java
y1, m1, d1 = start
y2, m2, d2 = end
delta = date(*end) - date(*start)
if delta <= 365:
if is_leap_year(y1) and is_leap_year(y2):
denom = 366
elif is_leap_year(y1) and date(y1, m1, d1) <= date(y1, 2, 29):
denom = 366
elif is_leap_year(y2) and date(y2, m2, d2) >= date(y2, 2, 29):
denom = 366
else:
denom = 365
else:
year_range = range(y1, y2 + 1)
nb = 0
for y in year_range:
nb += 366 if is_leap_year(y) else 365
denom = nb / len(year_range)
return delta / denom
if not is_number(start_date):
raise TypeError("start_date %s must be a number" % str(start_date))
if not is_number(end_date):
raise TypeError("end_date %s must be number" % str(end_date))
if start_date < 0:
raise ValueError("start_date %s must be positive" % str(start_date))
if end_date < 0:
raise ValueError("end_date %s must be positive" % str(end_date))
if start_date > end_date: # switch dates if start_date > end_date
temp = end_date
end_date = start_date
start_date = temp
y1, m1, d1 = date_from_int(start_date)
y2, m2, d2 = date_from_int(end_date)
if basis == 0: # US 30/360
d2 = 30 if d2 == 31 and (d1 == 31 or d1 == 30) else min(d2, 31)
d1 = 30 if d1 == 31 else d1
count = 360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)
result = count / 360
elif basis == 1: # Actual/actual
result = actual_nb_days_AFB_alter((y1, m1, d1), (y2, m2, d2))
elif basis == 2: # Actual/360
result = (end_date - start_date) / 360
elif basis == 3: # Actual/365
result = (end_date - start_date) / 365
elif basis == 4: # Eurobond 30/360
d2 = 30 if d2 == 31 else d2
d1 = 30 if d1 == 31 else d1
count = 360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)
result = count / 360
else:
raise ValueError("%d must be 0, 1, 2, 3 or 4" % basis)
return result
def yearfrac(start_date, end_date, basis=0):
"""
Calculates the fraction of the year represented by the number of whole days between
two dates (the start_date and the end_date). Use the YEARFRAC worksheet function to
identify the proportion of a whole year's benefits or obligations to assign to a
specific term.
:param start_date: Required. A date that represents the start date.
:param end_date: Required. A date that represents the end date.
:param basis: Optional. The type of day count basis to use.
:return: Fraction of the year between the two dates.
.. reference::
https://support.office.com/en-us/article/YEARFRAC-function-3844141e-c76d-4143-82b6-208454ddc6a8
.. remarks::
* Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations.
By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448
because it is 39,448 days after January 1, 1900.
* All arguments are truncated to integers.
* If start_date or end_date are not valid dates, YEARFRAC returns the #VALUE! error value.
* If basis < 0 or if basis > 4, YEARFRAC returns the #NUM! error value.
"""
def actual_nb_days_ISDA(start, end): # needed to separate days_in_leap_year from days_not_leap_year
y1, m1, d1 = start
y2, m2, d2 = end
days_in_leap_year = 0
days_not_in_leap_year = 0
year_range = range(y1, y2 + 1)
for y in year_range:
if y == y1 and y == y2:
nb_days = date(y2, m2, d2) - date(y1, m1, d1)
elif y == y1:
nb_days = date(y1 + 1, 1, 1) - date(y1, m1, d1)
elif y == y2:
nb_days = date(y2, m2, d2) - date(y2, 1, 1)
else:
nb_days = 366 if is_leap_year(y) else 365
if is_leap_year(y):
days_in_leap_year += nb_days
else:
days_not_in_leap_year += nb_days
return days_not_in_leap_year, days_in_leap_year
def actual_nb_days_AFB_alter(start, end):
"""
.. note::
Converted from: http://svn.finmath.net/finmath%20lib/trunk/src/main/java/net/finmath/time/daycount/DayCountConvention_ACT_ACT_YEARFRAC.java
"""
y1, m1, d1 = start
y2, m2, d2 = end
delta = date(*end) - date(*start)
if delta <= 365:
if is_leap_year(y1) and is_leap_year(y2):
denom = 366
elif is_leap_year(y1) and date(y1, m1, d1) <= date(y1, 2, 29):
denom = 366
elif is_leap_year(y2) and date(y2, m2, d2) >= date(y2, 2, 29):
denom = 366
else:
denom = 365
else:
year_range = range(y1, y2 + 1)
nb = 0
for y in year_range:
nb += 366 if is_leap_year(y) else 365
denom = nb / len(year_range)
return delta / denom
if not is_number(start_date):
raise TypeError("start_date %s must be a number" % str(start_date))
if not is_number(end_date):
raise TypeError("end_date %s must be number" % str(end_date))
if start_date < 0:
raise ValueError("start_date %s must be positive" % str(start_date))
if end_date < 0:
raise ValueError("end_date %s must be positive" % str(end_date))
# switch dates if start_date > end_date
if start_date > end_date:
temp = end_date
end_date = start_date
start_date = temp
y1, m1, d1 = date_from_int(start_date)
y2, m2, d2 = date_from_int(end_date)
# US 30/360
if basis == 0:
d2 = 30 if d2 == 31 and (d1 == 31 or d1 == 30) else min(d2, 31)
d1 = 30 if d1 == 31 else d1
count = 360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)
result = count / 360
# Actual/actual
elif basis == 1:
result = actual_nb_days_AFB_alter((y1, m1, d1), (y2, m2, d2))
# Actual/360
elif basis == 2:
result = (end_date - start_date) / 360
# Actual/365
elif basis == 3:
result = (end_date - start_date) / 365
# Eurobond 30/360
elif basis == 4:
d2 = 30 if d2 == 31 else d2
d1 = 30 if d1 == 31 else d1
result = (360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)) / 360
else:
raise ValueError("%d must be 0, 1, 2, 3 or 4" % basis)
return result
def test_is_number():
assert is_number(1)
assert is_number(0)
assert is_number(-1)
assert is_number(1.0)
assert is_number(0.0)
assert is_number(-1.0)
assert is_number('1.0')
assert is_number('0.0')
assert is_number('-1.0')
assert is_number(True)
assert is_number(False)
assert not is_number(None)
assert not is_number('x')
def yearfrac(
start_date,
end_date,
basis=0
): # Excel reference: https://support.office.com/en-us/article/YEARFRAC-function-3844141e-c76d-4143-82b6-208454ddc6a8
def actual_nb_days_ISDA(
start, end
): # needed to separate days_in_leap_year from days_not_leap_year
y1, m1, d1 = start
y2, m2, d2 = end
days_in_leap_year = 0
days_not_in_leap_year = 0
year_range = list(range(y1, y2 + 1))
for y in year_range:
if y == y1 and y == y2:
nb_days = date(y2, m2, d2) - date(y1, m1, d1)
elif y == y1:
nb_days = date(y1 + 1, 1, 1) - date(y1, m1, d1)
elif y == y2:
nb_days = date(y2, m2, d2) - date(y2, 1, 1)
else:
nb_days = 366 if is_leap_year(y) else 365
if is_leap_year(y):
days_in_leap_year += nb_days
else:
days_not_in_leap_year += nb_days
return (days_not_in_leap_year, days_in_leap_year)
def actual_nb_days_AFB_alter(
start, end
): # http://svn.finmath.net/finmath%20lib/trunk/src/main/java/net/finmath/time/daycount/DayCountConvention_ACT_ACT_YEARFRAC.java
y1, m1, d1 = start
y2, m2, d2 = end
delta = date(*end) - date(*start)
if delta <= 365:
if is_leap_year(y1) and is_leap_year(y2):
denom = 366
elif is_leap_year(y1) and date(y1, m1, d1) <= date(y1, 2, 29):
denom = 366
elif is_leap_year(y2) and date(y2, m2, d2) >= date(y2, 2, 29):
denom = 366
else:
denom = 365
else:
year_range = list(range(y1, y2 + 1))
nb = 0
for y in year_range:
nb += 366 if is_leap_year(y) else 365
denom = nb / len(year_range)
return delta / denom
if not is_number(start_date):
raise TypeError("start_date %s must be a number" % str(start_date))
if not is_number(end_date):
raise TypeError("end_date %s must be number" % str(end_date))
if start_date < 0:
raise ValueError("start_date %s must be positive" % str(start_date))
if end_date < 0:
raise ValueError("end_date %s must be positive" % str(end_date))
if start_date > end_date: # switch dates if start_date > end_date
temp = end_date
end_date = start_date
start_date = temp
y1, m1, d1 = date_from_int(start_date)
y2, m2, d2 = date_from_int(end_date)
if basis == 0: # US 30/360
d2 = 30 if d2 == 31 and (d1 == 31 or d1 == 30) else min(d2, 31)
d1 = 30 if d1 == 31 else d1
count = 360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)
result = count / 360
elif basis == 1: # Actual/actual
result = actual_nb_days_AFB_alter((y1, m1, d1), (y2, m2, d2))
elif basis == 2: # Actual/360
result = (end_date - start_date) / 360
elif basis == 3: # Actual/365
result = (end_date - start_date) / 365
elif basis == 4: # Eurobond 30/360
d2 = 30 if d2 == 31 else d2
d1 = 30 if d1 == 31 else d1
count = 360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)
result = count / 360
else:
raise ValueError("%d must be 0, 1, 2, 3 or 4" % basis)
return result
def test_is_number(data, result):
assert is_number(data) == result
def test_is_number():
assert is_number(1)
assert is_number(0)
assert is_number(-1)
assert is_number(1.0)
assert is_number(0.0)
assert is_number(-1.0)
assert is_number('1.0')
assert is_number('0.0')
assert is_number('-1.0')
assert is_number(True)
assert is_number(False)
assert not is_number(None)
assert not is_number('x')
def test_is_number(data, expected):
assert is_number(data) == expected
def yearfrac(start_date, end_date, basis=0):
# Excel reference: https://support.office.com/en-us/article/
# YEARFRAC-function-3844141e-c76d-4143-82b6-208454ddc6a8
def actual_nb_days_afb_alter(beg, end):
# http://svn.finmath.net/finmath%20lib/trunk/src/main/java/net/
# finmath/time/daycount/DayCountConvention_ACT_ACT_YEARFRAC.java
delta = date(*end) - date(*beg)
if delta <= 365:
if (is_leap_year(beg[0]) and date(*beg) <= date(beg[0], 2, 29) or
is_leap_year(end[0]) and date(*end) >= date(end[0], 2, 29)
or is_leap_year(beg[0]) and is_leap_year(end[0])):
denom = 366
else:
denom = 365
else:
year_range = range(beg[0], end[0] + 1)
nb = 0
for y in year_range:
nb += 366 if is_leap_year(y) else 365
denom = nb / len(year_range)
return delta / denom
if not is_number(start_date):
raise TypeError("start_date %s must be a number" % str(start_date))
if not is_number(end_date):
raise TypeError("end_date %s must be number" % str(end_date))
if start_date < 0:
raise ValueError("start_date %s must be positive" % str(start_date))
if end_date < 0:
raise ValueError("end_date %s must be positive" % str(end_date))
if start_date > end_date: # switch dates if start_date > end_date
start_date, end_date = end_date, start_date
y1, m1, d1 = date_from_int(start_date)
y2, m2, d2 = date_from_int(end_date)
if basis == 0: # US 30/360
d1 = min(d1, 30)
d2 = max(d2, 30) if d1 == 30 else d2
day_count = 360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)
result = day_count / 360
elif basis == 1: # Actual/actual
result = actual_nb_days_afb_alter((y1, m1, d1), (y2, m2, d2))
elif basis == 2: # Actual/360
result = (end_date - start_date) / 360
elif basis == 3: # Actual/365
result = (end_date - start_date) / 365
elif basis == 4: # Eurobond 30/360
d2 = min(d2, 30)
d1 = min(d1, 30)
day_count = 360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)
result = day_count / 360
else:
raise ValueError("basis: %d must be 0, 1, 2, 3 or 4" % basis)
return result
def match(lookup_value, lookup_range, match_type=1): # Excel reference: https://support.office.com/en-us/article/MATCH-function-e8dffd45-c762-47d6-bf89-533f4a37673a
if list_like(lookup_range) is False:
return ExcelError('#VALUE!', 'Lookup_range is not a Range')
def type_convert(value):
if type(value) == str:
value = value.lower()
elif type(value) == int:
value = float(value)
elif value is None:
value = 0
return value
def type_convert_float(value):
if is_number(value):
value = float(value)
else:
value = None
return value
lookup_value = type_convert(lookup_value)
range_values = [item for t in lookup_range for item in t if t is not None] # filter None values to avoid asc/desc order errors
# range_values = [x for x in lookup_range[0]) if x is not None] # filter None values to avoid asc/desc order errors
range_length = len(range_values)
if match_type == 1:
# Verify ascending sort
posMax = -1
for i in range(range_length):
current = type_convert(range_values[i])
if i < range_length - 1:
if current > type_convert(range_values[i + 1]):
return ExcelError('#VALUE!', 'for match_type 1, lookup_range must be sorted ascending')
if current <= lookup_value:
posMax = i
if posMax == -1:
return ExcelError('#VALUE!','no result in lookup_range for match_type 1')
return posMax +1 #Excel starts at 1
elif match_type == 0:
# No string wildcard
try:
if is_number(lookup_value):
lookup_value = float(lookup_value)
output = [type_convert_float(x) for x in range_values].index(lookup_value) + 1
else:
output = [str(x).lower() for x in range_values].index(lookup_value) + 1
return output
except:
return ExcelError('#VALUE!', '%s not found' % lookup_value)
elif match_type == -1:
# Verify descending sort
posMin = -1
for i in range((range_length)):
current = type_convert(range_values[i])
if i is not range_length-1 and current < type_convert(range_values[i+1]):
return ExcelError('#VALUE!','for match_type -1, lookup_range must be sorted descending')
if current >= lookup_value:
posMin = i
if posMin == -1:
return ExcelError('#VALUE!', 'no result in lookup_range for match_type -1')
return posMin +1 #Excel starts at 1