def countif(rng, criteria):
# Excel reference: https://support.office.com/en-us/article/
# COUNTIF-function-e0de10c6-f885-4e71-abb4-1f464816df34
if not list_like(rng):
rng = ((rng, ), )
valid = find_corresponding_index(rng, criteria)
return len(valid)
def vlookup(lookup_value, table_array, col_index_num, range_lookup=True):
""" Vertical Lookup
:param lookup_value: value to match (value or cell reference)
:param table_array: range of cells being searched.
:param col_index_num: column number to return
:param range_lookup: True, assumes sorted, finds nearest. False: find exact
:return: #N/A if not found else value
"""
# Excel reference: https://support.office.com/en-us/article/
# VLOOKUP-function-0BBC8083-26FE-4963-8AB8-93A18AD188A1
if not list_like(table_array):
return NA_ERROR
if col_index_num <= 0:
return '#VALUE!'
if col_index_num > len(table_array[0]):
return REF_ERROR
result_idx = _match(lookup_value, [row[0] for row in table_array],
match_type=bool(range_lookup))
if isinstance(result_idx, int):
return table_array[result_idx - 1][col_index_num - 1]
else:
# error string
return result_idx
def hlookup(lookup_value, table_array, row_index_num, range_lookup=True):
""" Horizontal Lookup
:param lookup_value: value to match (value or cell reference)
:param table_array: range of cells being searched.
:param row_index_num: column number to return
:param range_lookup: True, assumes sorted, finds nearest. False: find exact
:return: #N/A if not found else value
"""
# Excel reference: https://support.office.com/en-us/article/
# hlookup-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f
if not list_like(table_array):
return NA_ERROR
if row_index_num <= 0:
return VALUE_ERROR
if row_index_num > len(table_array):
return REF_ERROR
result_idx = _match(lookup_value,
table_array[0],
match_type=bool(range_lookup))
if isinstance(result_idx, int):
return table_array[row_index_num - 1][result_idx - 1]
else:
# error string
return result_idx
def sumifs(sum_range, *args):
# Excel reference: https://support.office.com/en-us/article/
# SUMIFS-function-C9E748F5-7EA7-455D-9406-611CEBCE642B
if not list_like(sum_range):
sum_range = ((sum_range, ), )
return sum(
_numerics((sum_range[r][c] for r, c in handle_ifs(args, sum_range)),
keep_bools=True))
def averageifs(average_range, *args):
# Excel reference: https://support.office.com/en-us/article/
# AVERAGEIFS-function-48910C45-1FC0-4389-A028-F7C5C3001690
if not list_like(average_range):
average_range = ((average_range, ), )
coords = handle_ifs(args, average_range)
data = _numerics((average_range[r][c] for r, c in coords), keep_bools=True)
if len(data) == 0:
return DIV0
return sum(data) / len(data)
def minifs(min_range, *args):
# Excel reference: https://support.office.com/en-us/article/
# minifs-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599
if not list_like(min_range):
min_range = ((min_range, ), )
try:
return min(
_numerics(
(min_range[r][c] for r, c in handle_ifs(args, min_range)),
keep_bools=True))
except ValueError:
return 0
def maxifs(max_range, *args):
# Excel reference: https://support.office.com/en-us/article/
# maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883
if not list_like(max_range):
max_range = ((max_range, ), )
try:
return max(
_numerics(
(max_range[r][c] for r, c in handle_ifs(args, max_range)),
keep_bools=True))
except ValueError:
return 0
def index(array, row_num, col_num=None):
# Excel reference: https://support.office.com/en-us/article/
# index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd
if not list_like(array) or not list_like(array[0]):
return VALUE_ERROR
try:
# rectangular array
if row_num and col_num:
return array[row_num - 1][col_num - 1]
elif row_num:
if len(array[0]) == 1:
return array[row_num - 1][0]
elif len(array) == 1:
return array[0][row_num - 1]
elif isinstance(array, np.ndarray):
return array[row_num - 1, :]
else:
return (tuple(array[row_num - 1]), )
elif col_num:
if len(array) == 1:
return array[0][col_num - 1]
elif len(array[0]) == 1:
return array[col_num - 1][0]
elif isinstance(array, np.ndarray):
result = array[:, col_num - 1]
result.shape = result.shape + (1, )
return result
else:
return tuple((r[col_num - 1], ) for r in array)
except IndexError:
pass
return NA_ERROR
def lookup(lookup_value, lookup_array, result_range=None):
"""
There are two ways to use LOOKUP: Vector form and Array form
Vector form: lookup_array is list like (ie: n x 1)
Array form: lookup_array is rectangular (ie: n x m)
First row or column is the lookup vector.
Last row or column is the result vector
The longer dimension is the search dimension
:param lookup_value: value to match (value or cell reference)
:param lookup_array: range of cells being searched.
:param result_range: (optional vector form) values are returned from here
:return: #N/A if not found else value
"""
# Excel reference: https://support.office.com/en-us/article/
# lookup-function-446d94af-663b-451d-8251-369d5e3864cb
if not list_like(lookup_array):
return NA_ERROR
height = len(lookup_array)
width = len(lookup_array[0])
# match across the largest dimension
if width <= height:
match_idx = _match(lookup_value, tuple(i[0] for i in lookup_array))
result = tuple(i[-1] for i in lookup_array)
else:
match_idx = _match(lookup_value, lookup_array[0])
result = lookup_array[-1]
if len(lookup_array) > 1 and len(lookup_array[0]) > 1:
# rectangular array
assert result_range is None
elif result_range:
if len(result_range) > len(result_range[0]):
result = tuple(i[0] for i in result_range)
else:
result = result_range[0]
if isinstance(match_idx, int):
return result[match_idx - 1]
else:
# error string
return match_idx