def average(*args): # Excel reference: https://support.microsoft.com/en-us/office/ # average-function-047bac88-d466-426c-a32b-8f33eb960cf6 data = _numerics(*args) # A returned string is an error code if isinstance(data, str): return data elif len(data) == 0: return DIV0 else: return sum(data) / len(data)
def min_(*args): # Excel reference: https://support.microsoft.com/en-us/office/ # min-function-61635d12-920f-4ce2-a70f-96f202dcc152 data = _numerics(*args) # A returned string is an error code if isinstance(data, str): return data # however, if no non numeric cells, return zero (is what excel does) elif len(data) < 1: return 0 else: return min(data)
def max_(*args): # Excel reference: https://support.microsoft.com/en-us/office/ # max-function-e0012414-9ac8-4b34-9a47-73e662c08098 data = _numerics(*args) # A returned string is an error code if isinstance(data, str): return data # however, if no non numeric cells, return zero (is what excel does) elif len(data) < 1: return 0 else: return max(data)
def averageifs(average_range, *args): # Excel reference: https://support.microsoft.com/en-us/office/ # AVERAGEIFS-function-48910C45-1FC0-4389-A028-F7C5C3001690 if not list_like(average_range): average_range = ((average_range, ), ) coords = handle_ifs(args, average_range) # A returned string is an error code if isinstance(coords, str): return coords 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 small(array, k): # Excel reference: https://support.microsoft.com/en-us/office/ # small-function-17da8222-7c82-42b2-961b-14c45384df07 data = _numerics(array, to_number=coerce_to_number) if isinstance(data, str): return data k = coerce_to_number(k) if isinstance(k, str): return VALUE_ERROR if not data or k is None or k < 1 or k > len(data): return NUM_ERROR k = math.ceil(k) return nsmallest(k, data)[-1]
def large(array, k): # Excel reference: https://support.microsoft.com/en-us/office/ # large-function-3af0af19-1190-42bb-bb8b-01672ec00a64 data = _numerics(array, to_number=coerce_to_number) if isinstance(data, str): return data k = coerce_to_number(k) if isinstance(k, str): return VALUE_ERROR if not data or k is None or k < 1 or k > len(data): return NUM_ERROR k = math.ceil(k) return nlargest(k, data)[-1]
def minifs(min_range, *args): # Excel reference: https://support.microsoft.com/en-us/office/ # minifs-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599 if not list_like(min_range): min_range = ((min_range, ), ) try: coords = handle_ifs(args, min_range) # A returned string is an error code if isinstance(coords, str): return coords return min( _numerics((min_range[r][c] for r, c in coords), keep_bools=True)) except ValueError: return 0
def maxifs(max_range, *args): # Excel reference: https://support.microsoft.com/en-us/office/ # maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883 if not list_like(max_range): max_range = ((max_range, ), ) try: coords = handle_ifs(args, max_range) # A returned string is an error code if isinstance(coords, str): return coords return max( _numerics((max_range[r][c] for r, c in coords), keep_bools=True)) except ValueError: return 0
def test_numerics(): assert (1, 3, 2, 3.1) == _numerics(1, '3', 2.0, pytest, 3.1, 'x') assert (1, 2, 3.1) == _numerics((1, '3', 2.0, pytest, 3.1, 'x'))
def test_numerics(): assert (1, 2, 3.1) == _numerics(1, '3', 2.0, pytest, 3.1, 'x') assert (1, 2, 3.1) == _numerics((1, '3', (2.0, pytest, 3.1), 'x'))