def sumproduct(*args):
# Excel reference: https://support.office.com/en-us/article/
# SUMPRODUCT-function-16753E75-9F68-4874-94AC-4D2145A2FD2E
# find any errors
error = next((i for i in flatten(args) if i in ERROR_CODES), None)
if error:
return error
# verify array sizes match
sizes = set()
for arg in args:
assert isinstance(arg, tuple), isinstance(arg[0], tuple)
sizes.add((len(arg), len(arg[0])))
if len(sizes) != 1:
return VALUE_ERROR
# put the values into numpy vectors
values = np.array(
tuple(
tuple(x if isinstance(x, (float,
int)) and not isinstance(x, bool) else 0
for x in flatten(arg)) for arg in args))
# return the sum product
return np.sum(np.prod(values, axis=0))
def _dec2base(value, places=None, base=16):
value = list(flatten(value))
if len(value) != 1 or isinstance(value[0], bool):
return VALUE_ERROR
value = value[0]
if value in ERROR_CODES:
return value
if value in (None, EMPTY):
if base == 8:
return NUM_ERROR
value = 0
try:
value = int(value)
except ValueError:
return VALUE_ERROR
mask = _SIZE_MASK[base]
if not (-mask <= value < mask):
return NUM_ERROR
if value < 0:
value += mask << 1
value = _BASE_TO_FUNC[base](value)[2:].upper()
if places is None:
places = 0
else:
places = int(places)
if places < len(value):
return NUM_ERROR
return value.zfill(int(places))
def product(*args):
list = []
for arg in flatten(args):
if arg:
list.append(arg)
if list:
return np.prod(list)
else:
return 0
def _numerics(*args, keep_bools=False, to_number=lambda x: x):
# ignore non numeric cells
args = tuple(flatten(args))
error = next((x for x in args if x in ERROR_CODES), None)
if error is not None:
# return the first error in the list
return error
else:
args = (to_number(a) for a in args
if keep_bools or not isinstance(a, bool))
return tuple(x for x in args if isinstance(x, (int, float)))
def concatenate(*args):
# Excel reference: https://support.office.com/en-us/article/
# CONCATENATE-function-8F8AE884-2CA8-4F7A-B093-75D702BEA31D
if tuple(flatten(args)) != args:
return VALUE_ERROR
error = next((x for x in args if x in ERROR_CODES), None)
if error:
return error
return ''.join(coerce_to_string(a) for a in args)
def _base2dec(value, base):
value = list(flatten(value))
if len(value) != 1 or isinstance(value[0], bool):
return VALUE_ERROR
value = value[0]
if value in ERROR_CODES:
return value
if value in (None, EMPTY):
value = '0'
elif isinstance(value, (int, float)) and value >= 0:
if int(value) == value:
value = str(int(value))
if isinstance(value, str) and len(value) <= 10:
try:
value, mask = int(value, base), _SIZE_MASK[base]
if value >= 0:
return (value & ~mask) - (value & mask)
except ValueError:
return NUM_ERROR
return NUM_ERROR
def count(*args):
# Excel reference: https://support.office.com/en-us/article/
# COUNT-function-a59cd7fc-b623-4d93-87a4-d23bf411294c
return sum(1 for x in flatten(args)
if isinstance(x, (int, float)) and not isinstance(x, bool))
def concat(*args):
# Excel reference: https://support.office.com/en-us/article/
# concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2
return concatenate(*tuple(flatten(args)))