Free Support Forum - aspose.com

CalculateFormula Coming up with 0 for all formulas

Aspose.Cells Product Family
#1

I'm using Aspose.Cells for .NET v4.4.0.0 in a .NET 1.1 web application.

The spreadsheet template file we use was recently changed to make heavy use of the "OFFSET" and "MATCH" Excel functions. The application copies the template file and performs a CalculateFormula(false) call to calculate the formulas. After the calculation is complete the cell values are copied (cell by cell to another copy of a "breakout" spreadsheet template. If I open the source template spreadsheet in Excel, I see the values I expect. The breakout spreadsheets, however have all zeros in them.

Using the debugger I have halted my program immediately after the CalculateFormula(false) method call to examine the calculated values. All values are zero...including the cells where I see the expected values in Excel. There appears to be some sort of issue with the the handling of OFFSET() and MATCH() functions. The levels of Excel function calls are fairly deep and I'm wondering if Aspose.Cells is having a problem dealing with the depth. Has the CalculateFormula() method been tested with MATCH() function calls made within an OFFSET() call such as OFFSET(reference, MATCH(lookupval1,lookuparr1,0),MATCH(lookupval2,lookuparr2, 0))?

Is there any way to determine which portion of my spreadsheet that the calculation engine is having problems dealing with? It is my understanding from reading other posts that the calculation engine "ignores" formulas that it does not understand (by setting the values to 0)? Is there a way to determine where the parser is "giving up".

Thanks,

Mark

#2

Hi Mark,

Could you please post your template file here? I will check this issue ASAP.

#3

Laurence,

I'm not allowed to post the entire XLS file because of intellectual property rights issues. I do have permission to post one of the formulas that is causing the error.

=OFFSET('Revenue & Admin Calculations'!$F$3,MATCH($A14&Lookup!$P$2,'Revenue & Admin Calculations'!$A$4:$A$199,0),MATCH(G$12,'Revenue & Admin Calculations'!$G$2:$BZ$2,0)+G$10)

By stopping the debugger and substituting in parts of the formula and then calling CalculateFormula(false) I believe I have been able to rule out problems with the MATCH() calls. For example, if I enter the formula

=MATCH($A14&Lookup!$P$2,'Revenue & Admin Calculations'!$A$4:$A$199,0)

call CalculateFormula(false), and then check the Value property of the cell, the value is 73 which for this particular formula is correct.

Likewise, if I enter the formula

=MATCH(G$12,'Revenue & Admin Calculations'!$G$2:$BZ$2,0)+G$10

call CalculateFormula(false), and then check the cell's Value property it is 61 which for this particular formula is also correct.

Therefore, the problem seems to be in either the OFFSET() function itself or the resolution of the first parameter (the reference parameter) in the OFFSET() function call. I have tried changing the name of the sheet from "'Revenue & Admin Calculations" to "Revenue_Admin Calculations" and it did not change the behavior. The reference cell is an empty cell directly above a list of lookup values on the "Revenue & Admin Calculations" sheet. The reference cell is merged with the cell above it which contains text for a heading. I have tried "unmerging" the cells in my template to see if that would change the behavior, but it did not.

I also tried replacing the two MATCH() function calls with their results (see previous paragraphs) directly into the formula to yield the formula

=OFFSET('Revenue & Admin Calculations'!$F$3,73,61)

But this formula also returned the same 0 result.

Any assistance you can provide will be helpful. Also, is there a diagnostic switch or something we can enable to find out where parser is having trouble?

Thanks,

Mark

#4

Hi Mark,

Thanks for the detailed explanation.

Yes, there seems to have some sort of problem with OFFSET formula. We will figure out the issue soon.

Thank you.

#5

Amjad,

We did some more testing and the problem seems to stem from an inability for the OFFSET() function to handle references (i.e. the first function argument) to another sheet in the workbook. If the reference is on the same sheet as the formula that uses the OFFSET() function, the calculations appear to work.

Unfortuately, I cannot restructure our template to move the references to the same sheet that has the formula that uses the OFFSET() function. So we desparately need this corrected. It is currently holding up a production application deployment. Can you provide me with an estimated date for completion of the correction?

Thanks,

Mark

#6

Hi Mark,

Thanks for considering Aspose.

Please try the attached version. Hopefully, it will resolve the issue related OFFSET formula.

Thank you.

#7

Amjad,

It is working better. I'm now seeing expected values for most of the cells. However I do see a few cells where the value is now "#REF!" where it was 0 before. I'll see if I can gather some more information about the cells that are displaying "#REF!" and post those details later.

Note that these cells do not display "#REF!" if I open the template in Excel.

Thank you,

Mark

#8

Amjad,

I did some additional testing and here is what I've found regarding the cells that are displaying "#REF!"

One of the cells that is displaying "#REF!" has this formula:

=OFFSET('Expense Calculations'!$G$3,MATCH($A18&Lookup!$P$37,'Expense Calculations'!$A$4:$A$311,0),MATCH(B$12,'Expense Calculations'!$H$2:$CA$2,0)+B$10)

The formula in the cell above it has this formula and is working correctly:

=OFFSET('Expense Calculations'!$G$3,MATCH($A17&Lookup!$P$37,'Expense Calculations'!$A$4:$A$311,0),MATCH(B$12,'Expense Calculations'!$H$2:$CA$2,0)+B$10)

The only difference between the two formulas I see is the first parameter of the first MATCH() formula inside the OFFSET() formula. The non-functioning formula refers to cell $A18 in its first MATCH() formula. The functioning formula above it refers to cell $A17 in its first MATCH() statement.

Cell $A18 has the text value "Non-ABD DDD" which is obtained via the formula "=Lookup!$C$16".

Cell $A17 has the text value "ABD w/ Medicare - Non-DDD" which is obtained via the formula "=Lookup!$C$4".

I broke the OFFSET formula down and entered each MATCH() formula alone. As I have seen before, even in the cell which is returning "#REF!", the MATCH() formulas alone return the expected values. I also tried substituting the returned MATCH() formula values directly into the OFFSET() function, but I still receive "#REF!" in the cell.

Can you see if you can locate the problem. I think you're close to having it operational.

Thank you,

Mark

#9

Hi Mark,

We will check and figure out the issue soon.

And could you post us your template file. We sign NDA (Non-Disclosure Agreement) with you. For your info, only you and Aspose Authorized staff will be able to download your attachment. No other user can check your attachments. Don't worry we always take care of our clients materials. Our customes always rely on us to post their templates or projects to figure out the issues. If this is not possible, could you create a dummy template file with fake type of data which could produce the issue you are mentioning about and post it here. It will be relatively easier for us to reproduce the problem and sort out the things very soon.

Thank you.

#10

Amjad,

I'll see if we can devise a "stripped down" version of the template that still exhibits the calculation problem, but does not violate our intellectual property responsibilities. Once I've done that, I'll forward the template to you.

Mark

#11

Hi Mark,

Thanks, we will look forward to your workaround.

#12

Amjad,

I believe we've discovered the failure mode for the Aspose.Cells CalculateFormula() when calculating the OFFSET() formulas in our spreadsheet. I'm attaching a simple workbook with two worksheets in it that exhibits the problem. The sheet named "Sheet1" has two different OFFSET() formulas in it. The first OFFSET() formula in cell A1 works fine. The second OFFSET() formula in cell A2 fails and returns "#REF!".

From our tests, it appears that any combination of OFFSET() formula input arguments that results in a value that exceeds 256, the OFFSET() formula is evaluated to "#REF!".

For instance if we enter the formula "=OFFSET(A253, 4,1)" in cell A253, call CalculateFormula(false), and then check cell A253's value, I find that it is "#REF!". However, if I modify the formula in A253 to be "=OFFSET(A253, 3, 1)", call CalculateFormula(false), and then check the value of cell A253, it is 256.

Just theorizing, but I'd bet the OFFSET() formula row calculation processing within the component is trying to do integer arithmetic on a byte data type.

Let me know if you have any questions about the attached sample or our findings. Please let me know when you can provide a correction.

Thank you,

Mark

#13

Hi Mark,

Thank you for the sample file. It does help us to figure out the problem. Please try this attached fix.

#14

Laurence,

This update seems to have fixed the problem. I'll let you know if I determine otherwise.

Thank you for you prompt support.

Mark

#15

Laruence,

We found that the =OFFSET() formula problem is still occuring with the v4.4.0.3 update albeit on a much more limited basis. I'm still receiving 0 values for cells containing the following OFFSET calls in Excel:

=OFFSET(reference, 308, 13)

=OFFSET(reference, 308, 61)

Cells with the following formulas are calculated properly (Note that third parameter in each of these cases is one more than the failing cases):

=OFFSET(reference, 308, 14)

=OFFSET(reference, 308, 62)

Note that the "reference" parameter in all of the above OFFSET() formulas refer to the same cell. It appears that there is some sort of arithmetic issue with the

Thank you,

Mark

#16

Hi Mark,

I tested with your template file with your mentioned formulas and it worked fine. Please try this attached version. And please verify if the formula does show 0 in MS Excel.

#17

Laurence,

I'm still seeing 0s in the same fields with v4.4.0.21.

Unfortunately I cannot post the template that is having the problem due to my company's proprietary information policy.

Mark

#18

Hi Mark,

We really appreciate that if you could post your template file, I think you can also try to use some dummy data to fill the workbook to reproduce the problem. This will really help us to figure out the issue soon.

Alternatively, you can send your template file and private posts at nanjing@aspose.com. And we always give the highest priority to our customers and deal their data with ultimate care.

Thank you.

#19

Amjad,

I don't think we'll need to post the template file. We managed to locate the source of the Aspose calculation errors that we're experiencing. The error appears to be caused by differences in the way Excel and Aspose.Cells calculation engines parse the SUMIF() function.

The OFFSET() formulas referenced in my earlier post referred to cells containing SUMIF() function calls. Aspose.Cells could not evaluate the SUMIF() functions because of format of one of the arguments to that function in our template file. The second parameter to the Excel SUMIF() function serves as the criteria to determine which cells provided in the first parameter should be summed. In our template, this value was specified as a range of merged cells. The Excel SUMIF() documentation specifies that this parameter should be in the form of a number, expression, or text. The Excel engine was correctly evaluating the SUMIF() formulas containing the merged cell range criteria values. The Aspose.Cells calculation engine does not. We were able to work around the Aspose.Cells calculation engine difference by replacing the merged cell range expression with a single cell reference.

You may want to address this issue in a future Aspose.Cells release.

Let me know if you need my assistance with reproducing the problem.

Thank you,

Mark

#20

Dear Mark,

Thanks for your information.

But I still cannot reproduce the problem as you described. Please check the attached file, in cell A6, there is a formula "=SUMIF(A2:A5,C1:D1,B2:B5)".

We don't find the difference.