This project has moved and is read-only. For the latest updates, please go here.

.Calculate fails for IF & Vlookup statements

Aug 27, 2014 at 10:17 AM
Edited Aug 27, 2014 at 10:18 AM
Hi, I'm currently using EPPlus 4.0 Beta 2 and I'm having a problem, whereby the .Calculate Method doesn't work for cells that have IF statements. If I run .Calculate on a worksheet (i.e. worksheet.Calculate()) and then try to find get the value within the cell that contains the IF statement, the value returned is #VALUE!. The IF statement works fine within Excel and the spreadsheet itself so it's just the Calculate Method that doesn't seem to be able to work out the correct value. The syntax that I'm using for the IF statement is as follows
XLS_Modules.Cells[rowid, column_ao].Formula = "IF(" + XLS_Modules.Cells[(row + 11), column_internal_parameter_values].Address + "," + XLS_Modules.Cells[(input_row + 1), column_ao].Address + ",0)";
Any ideas on what could be causing this?


Additionally I think the problem also exists for Vlookup statements. I haven't done as much investigation yet but for a cell with a Vlookup statement, I'm getting a returned value of #N/A. Again, the vlookup statement works fine within the Excel spreadsheet itself.

Cheers
Shav
Aug 28, 2014 at 4:24 PM
Hi dude,

I can confirm it does not work at all, I get #N/A everytime.

Just found out, THE HARD WAY... Today...

Cheers,

Sergio
Sep 3, 2014 at 8:39 AM
Hi,
could you create an issue and attach a workbook with and an example where this fails? I dont think there is any thing wrong with the IF function itself, but there might be errors that "bubbles up" from the cells that are referenced in the IF-statement.

/Mats
Sep 3, 2014 at 12:14 PM
Edited Sep 11, 2014 at 5:31 PM
Hi Mats, here's a simple example I created to replicate the problem. I've created a very basic worksheet with a number in B1, a TRUE in B3 and the IF statement in cell B5 referencing B3. The Excel worksheet works fine but unfortunately, I cannot assign the value of cell B5 to an internal string variable as it simply gets #VALUE!. Here's the code I've used to create the worksheet.

        public void XLS_Create_Testwb_Worksheet()
        {

            Trace.WriteLineIf(Debug_Toggle, "\r\n \r\n*************\r\nXLS_Testwb\r\n*************");
            Trace.WriteLineIf(Debug_Toggle, "In Method to create Testwb worksheet");

            string worksheet_name = "IF Test";
            Trace.WriteLineIf(Debug_Toggle, "Worksheet name is " + worksheet_name + " and location in Dimensioning Workbook is " + worksheet_number.ToString());
            Trace.WriteLineIf(Debug_Toggle, "Adding Worksheet to Workbook");
            XLS_Workbook.Workbook.Worksheets.Add(worksheet_name);
            XLS_Testwb = XLS_Workbook.Workbook.Worksheets[worksheet_number];
            XLS_Testwb.Name = worksheet_name;

            // Variable for result of .Calculate
            string result;

            // Column Identifier
            int columnlabel = 1;
            int columnid = 2;

            // Row Pointer
            int rowid = 1;

            XLS_Testwb.Cells[rowid, columnlabel].Value = "Number";
            XLS_Testwb.Cells[rowid, columnid].Value = 10;
            rowid +=2;

            XLS_Testwb.Cells[rowid, columnlabel].Value = "Boolean Value";
            XLS_Testwb.Cells[rowid, columnid].Value = true;
            rowid +=2;

            XLS_Testwb.Cells[rowid, columnlabel].Value = "Result";
            XLS_Testwb.Cells[rowid, columnid].Formula = "IF(" + XLS_Testwb.Cells[(rowid - 2), columnid].Address + "," + XLS_Testwb.Cells[(rowid - 4), columnid].Address + ",0)";

            XLS_Testwb.Cells.AutoFitColumns();
            
            XLS_Testwb.Calculate();
            result = XLS_Testwb.Cells[rowid, columnid].Value.ToString();
            Trace.WriteLineIf(Debug_Toggle, "The content of result is " + result);

        }
Another related issue I experienced when creating the above is that my result variable had to be of type string. If result was of type int and I tried result = Convert.ToInt16(XLS_Testwb.Cells[rowid, columnid].Value); my app would Build but not run due to A first chance exception of type 'System.IO.IOException'


Question, how do I go about attaching the worksheet created to this thread?

Cheers
Shav
Sep 9, 2014 at 11:28 AM
Hi,
   is there anyone that can help/advise with this?
Thanks
Shav
Sep 11, 2014 at 4:40 PM
Hi everybody,

I ran into this problem recently as well, specifically with if statements. For me, the cause of the problem was the fact that the formula was missing an expression (in my case the "False" expression). Excel handles this by displaying "False" in the cell.

The EEPlus calc engine however has strict checks in several places (If.cs & IfFunctionCompiler.cs -- as far as I can tell) that will throw a ExcelErrorValueException if the expression count is not 3.

I'm not entirely sure how to view this problem. In one hand, the easy answer is just to write complete if statements in Excel. On the other hand, the better solution would be to program in better expression check for IF statements.

Example:

This did not compute:
=IF(E18=B16, E19)

This does computer:
=IF(E18=B16, E19, 0)
Sep 11, 2014 at 5:11 PM
A temporary workaround (if you're working with the source files) is to change the Compile() function at line 57 in IfFunctionCompiler.cs to the following:
public override CompileResult Compile(IEnumerable<Expression> children, ParsingContext context)
        {
            //if(children.Count() < 3) throw new ExcelErrorValueException(eErrorType.Value);
            var args = new List<FunctionArgument>();
            Function.BeforeInvoke(context);
            var firstChild = children.ElementAt(0);
            var boolVal = (bool)firstChild.Compile().Result;
            args.Add(new FunctionArgument(boolVal));
            if (boolVal)
            {
                var val = new object();

                if(children.Count() < 3)
                    val = false;
                else
                    val = children.ElementAt(1).Compile().Result;

                args.Add(new FunctionArgument(val));
                args.Add(new FunctionArgument(null));
            }
            else
            {
                var val = new object();

                if(children.Count() < 3)
                    val = false;
                else
                    val = children.ElementAt(2).Compile().Result;

                args.Add(new FunctionArgument(null));
                args.Add(new FunctionArgument(val));
            }
            return Function.Execute(args, context);
        }
The check for expression count has been commented out, and in its place a check is done within the value setters whereby if the children count is less than 3 a FALSE value is subbed in. I realize this isn't a perfect solution. However, FALSE values are the least destructive on the surrounding calculations as they are omitted from things like AVERAGE, SUM, MAX etc.

Enjoy.
Sep 11, 2014 at 5:50 PM
Ah, this is interesting.... Ok, I always had 3 arguments in my IF statements. Now, the first argument was simply the address of a cell that contained a Boolean true value.
XLS_Testwb.Cells[rowid, columnid].Formula = "IF(" + XLS_Testwb.Cells[(rowid - 2), columnid].Address + "," + XLS_Testwb.Cells[(rowid - 4), columnid].Address + ",0)";
If I update the above to have a equation for the first argument, i.e. E18=true, it gets a little better because .Calculate sort of works. It returns the false value. So while it can't find the match for the true statement and fails to assign the correct result to my integer result, it is at least able to equate the IF statement and return the false value because it hasn't found a match for the true statement. Therefore, there's something up with the evaluation of the true element of the IF statement and its struggling to match the cell against a Boolean true located in the reference cell. So here's my new statement
XLS_Testwb.Cells[rowid, columnid].Formula = "IF(" + XLS_Testwb.Cells[(rowid - 2), columnid].Address + "=\"TRUE\"," + XLS_Testwb.Cells[(rowid - 4), columnid].Address + ",0)";
Note, I made one edit to my originally posted code. I had
XLS_Testwb.Cells[rowid, columnid].Value = "TRUE";
which I changed to the belowas I will assigning a Boolean value to a cell and not a string containing TRUE
XLS_Testwb.Cells[rowid, columnid].Value = true;
Any ideas on why the evaluation of the true element of the IF statement is failing. I feel like I'm close and have tried a few different syntaxes for =true, etc..., but no joy.

Cheers
Shav
Sep 12, 2014 at 5:00 PM
So after a bit more testing and putting in =TRUE for element 1 of the IF statement, I think the problem is not now actually the evaluation of the true statement but when trying to return a value when the true statement for element 1 has a match. This is the IF statement and when the evaluation for element 1 is true, I'm getting #VALUE!
            XLS_Testwb.Cells[rowid, columnid].Formula = "IF(" + XLS_Testwb.Cells[(rowid - 2), columnid].Address + "=TRUE," + XLS_Testwb.Cells[(rowid - 4), columnid].Address + ",0)";
Now, if I change the IF statement to return the Value as opposed to the Address for the true condition, .Calculate works and I get the correct value returned and assigned to my integer result within my program.
            XLS_Testwb.Cells[rowid, columnid].Formula = "IF(" + XLS_Testwb.Cells[(rowid - 2), columnid].Address + "=TRUE," + XLS_Testwb.Cells[(rowid - 4), columnid].Value + ",0)";
However, this won't work for me in reality because I need the IF statement within the Excel worksheet to reference the cell ((rowid - 4), columnid)) as opposed to having an IF statement using a static value upon program running. Basically, I need the IF statement to be dynamic within the Excel sheet

I need this within the Excel sheet
=IF(E18=TRUE, E19, 0)
and not this
=IF(E18=TRUE, 10, 0)


Is there anyone out there from Epplus that can shed some light on this. Is this a known or fixable thing within 4.0 Beta

Many thanks
Shav
Sep 18, 2014 at 12:04 PM
Anyone from the Epplus team could help me with this? I'm at a sticking point in my code and can't progress without some solution for this.

Cheers
Shav
Oct 2, 2014 at 2:22 PM
Hi,

the bug has now been found and eliminated... next version of 4.0 will contain the fix.

/Mats
Marked as answer by Shav on 10/5/2014 at 1:02 PM
Oct 5, 2014 at 9:02 PM
Ah super, thanks for this Mats

Shav
Oct 10, 2014 at 4:21 PM
Hi Mats,

Is this bug related to my issues 15067 and 15064 with using Excel Address's or were my fixes necessary?

Thanks,
Tyler
Oct 23, 2014 at 1:18 PM
Sorry for this late reply, Tyler! As you might have seen I have merged your fixes into the code. There will be a Beta 3 release very soon that will contain the fixes.
Feb 25, 2015 at 9:07 AM
Hi,

This bug still appears to be an issue in the latest versions of EEPlus.

Can you confirm that it's been resolved?
Mar 20, 2015 at 8:24 PM
Edited Mar 20, 2015 at 8:33 PM
Hi.

I have similar problem. These 2 versions of formula give different results:

IF(K39-K164-(K39 * J156-J164)<-J40;-(K39-K164-(K39 * J156-J164)-J40);0) gives wrong result

IF(K39-K164-(K39 * J156-J164)<-J40;-1 * (K39-K164-(K39 * J156-J164)-J40);0) gives right result

I can send XLS workbook with real data.
I have the latest version of EPPlus.

Peter
Mar 23, 2015 at 10:46 AM
Thank you Peter. If I understand your issue correctly it seems to be related to negating an expression within a parenthesis. I will have a look at this.
Mar 23, 2015 at 11:03 AM
Yes. Correct.