NOT does not work in the context of a range context

[heetbeet]

What actually happened

Pycel seems to return a value error if a NOT is applied to a range. Here is an example: =SUM(--NOT(Table1[b]))

This gives a #VALUE! error in pycel, I expected the summation of all the False elements, which is 3.

Alternatively, =SUM(--Table1[b]) returns the correct summation of all the True elements, which is also 3.

What was expected to happen

What is supposed to happen is that: Table1[b] returns a range -> NOT converts each element in that range to it's logical opposite (False, True, False, True, False, True) in this case -> -- is a typical Excel idiom to convert a boolean to a number (for True the first negative converts True to -1 and the second negative converts the -1 back to 1; for False it is converted to 0 and then to 0 again) -> SUM should then sum up all the 1 values to reach a total of 3.

Code Sample

your_workbook.xlsx

from pycel import ExcelCompiler
from openpyxl import load_workbook

workbook = load_workbook("your_workbook.xlsx")  
compiler = ExcelCompiler(excel=workbook)
print(compiler.evaluate(f"Sheet1!D2"))
print(compiler.evaluate(f"Sheet1!E2"))

This prints:

#VALUE!
3

Environment

Pycel Version b093fc5

[dgorissen]

Thanks for the report. Unfortunately I dont have the bandwidth to fix this but appreciate any pull requests 🙏