gen_graph cannot deal with a (complex) formula

[ghost]

Please note that Pycel deals with a file with hundreds of formula very well until:

I get to this cell called InputData!H50 the Excel is as follow:

=IFERROR(IF(year_modelStart>=year_baseCosts;2%;AVERAGE(L50:OFFSET(K50;0;MATCH(year_baseCosts;CA_Years;0))));2%)

New RangedNames feature deals with it properly and delivers to Python:

=IFERROR(IF('InputData'!$G$14>='InputData'!$G$15,2%,AVERAGE(L50:OFFSET(K50,0,MATCH('InputData'!$G$15,'InputData'!$L$5:$DG$5,0)))),2%)

So far so good but at some point I hit the wall in the emit function:

ss = args[0].emit(ast,context=context) + op + args[1].emit(ast,context=context)

IndexError, list out of range

With basically args[1].emit(ast,context=context) being out of range for some reason I cannot understand (it has passed dozens of if_error and it never called the def if_error function I created).

Not to be deterred, I add to my code the following try/except to by-pass eventual index issues (not a great idea when dealing with great code I'd suppose, but can one really resist looking over the hill?):

        try:
            ss = args[0].emit(ast,context=context) + op + args[1].emit(ast,context=context)
        except (IndexError):
            ss = args[0].emit(ast,context=context) + op       

Without surprise it comes back to haunt me:

Exception: Failed to compile cell InputData!H50 with expression iferror((2% if eval_cell("InputData!G14")>=(eval_cell("InputData!G15") if eval_cell("InputData!G15") is not None else float('inf')) else average(l50:offset(eval_cell("InputData!K50"),0,match(eval_cell("InputData!G15"),eval_range("InputData!L5:DG5"),0)))),2%): invalid syntax (, line 1)

Any idea where to start?

[ghost]

Did you ever encounter problems handling the % character in cells, like e.g. =2% because it seems that's the origin of the above issue.

The code documentation says % is considered an operator with reference to MS Office documentation: http://office.microsoft.com/en-us/excel-help/calculation-operators-and-precedence-HP010078886.aspx

Actually I don't think it is in the purpose of parsing the string since % is the same as saying /100, i.e. an operator / and an operand 100. More practically I would suggest to write as operator and 0.01 as operand since in Python it will save us a complex treatment to declare it as float() - try to print 1/100 it will return 0 unless specified a float, print 10.01 it will return 0.01

Any preference?

[ghost]

Ok half of the problem solved now I end up on:

Failed to compile cell InputData!H50 with expression iferror((20.01 if eval_cell("InputData!G14")>=(eval_cell("InputData!G15") if eval_cell("InputData!G15") is not None else float('inf')) else average(l50:offset(eval_cell("InputData!K50"),0,xmatch(eval_cell("InputData!G15"),eval_range("InputData!L5:DG5"),0)))),20.01): invalid syntax (, line 1)

[ghost]

Match function can be ambiguous in python, particularly when using in-built re., documented as xmatch in excellib for safety

[ghost]

Ach I think I got it, that's the offset function, not supported yet as explained on pycel's prologue

[ghost]

Narrowed down the issue to ranges with nested reference function, i.e. something looking like: cell:reference_function reference_function:cell reference_function:reference_function

Example: average(l50:offset(eval_cell("InputData!K50"),2,1))

Makes the whole thing burst into flame the minute one compiles... Sobering since literal range handling was quite an addictive feature Looks like I'll have to do some serious hacking to solve the problem

[ghost]

Ok seems that can be overcome through building virtual cells