Formula Calculation

Aspose.Cells Cloud Product Family
1

I have a monte carlo simulation I am running in the cloud based on an Excel spreadsheet. The process works in a loop, and goes through 10000 iterations. The way I have it set up, it goes through the cells that will hold the random variables and assigns them a random number based on a distribution. After it’s done assigning the new random numbers, I have to pull the resultant calculation from a single cell on another sheet.


So far, I’ve been using the workbook.CalculateFormula() after I’ve finished all random assignments and right before getting the final calculation from that single cell. Unfortunately, this takes an incredibly long amount of time because of that function.

But, from what I understood, calling that formula once should create the calculation chain that makes calculation much easier thereafter. Am I missing something? Would it be better if flipped the calculation chain off? Or, would I be able to run CalculateFormula once, then go through and change the required values as necessary, and then only run a direct formula on the response cell (would that calculate all its precedents?)?

What are your thoughts and experience?



2

Hi,

Yes, you can try to set the formula calculation chain to false if you need to call Workbook.CalculateFormula() method only once, see the document for your reference:

Alternatively, you may also try to call a formula directly, see the document for your reference:

Moreover, you may also consider using Worksheet.CalculateFormula() and Cell.Calculate() method to calculate all the formulas in a single sheet and in a single cell accordingly.

Hope, this helps a bit.

Thank you.

3

Which should I used if I must recalculate my spreadsheet 10,000 times?

4

Hi,


Well, in that case, you must not set CreateCalcChain to false. The first iteration would take some time but the all other next calculations would be efficient.

Thank you.

5

I believe I know what’s happening. After I perform a FormulaCalculation, I go back and rewrite essentially all the formulas used. So when I call the FormulaCalculation again, it has to recreate the chain once again. That must be why it takes so long.


I saw in another post, someone used something like FormulaCalculation(true, CustomFunction)… what is that second parameter in the FormulaCalculation method? Perhaps I could circumvent having to rewrite every formula by using a custom formula. (In fact, how I am rewriting formulas now is to replace a UDF I had created…)
6
Hi,

jpo645:
I believe I know what's happening. After I perform a FormulaCalculation, I go back and rewrite essentially all the formulas used. So when I call the FormulaCalculation again, it has to recreate the chain once again. That must be why it takes so long.

Could you provide us a sample console application (runnable), you may zip it prior attaching here to reproduce the issue on our end, we will check it soon.

jpo645:
I saw in another post, someone used something like FormulaCalculation(true, CustomFunction)... what is that second parameter in the FormulaCalculation method? Perhaps I could circumvent having to rewrite every formula by using a custom formula. (In fact, how I am rewriting formulas now is to replace a UDF I had created...)

Well, this is used for your own custom functions, see the document for your reference:
http://www.aspose.com/docs/display/cellsnet/Using+ICustomFunction+feature


Thank you.
7

Thank you! If I replace my custom function with this, and then simply iterate 10000 times (Without having to rewrite the formulas each time), do you believe that should speed things up considerably?

8

Hi,


I think it might not make much difference. We have optimized the Aspose.Cells formula calculation engine over the years and with each new release. We recommend you to kindly try our latest version (e.g v8.2.0) if you are not already using it. Also, we request you to kindly provide us a sample console application (runnable), you may zip it prior attaching here to reproduce the issue on our end, we will check it soon. We will check if we could enhance the formula calculation engine further.

Thank you.
9

I can’t really provide you even a console application as the work is proprietary. What I can do is provide you the code (slightly edited) where the bottleneck takes place:


int k;
for (k = 1; k <= 10000; k++)
{

int i;
double randomVariable;

Worksheet CurrentWorksheet = workbook.Worksheets[“Sheet1”];


for (i = 2; i <= 15; i++)
{
Cell cell = CurrentWorksheet.Cells[4, i];
// double randomVariable;
randomVariable = RandomTriangular(AnnualGSIIncreasesLower, AnnualGSIIncreasesMedian, AnnualGSIIncreasesHigh);
cell.Formula = “=” + alphabet[i - 1] + "5 * (1 + " + randomVariable.ToString() + “)”;
}


for (i = 2; i <= 15; i++)
{
Cell cell = CurrentWorksheet.Cells[5, i];
//double randomVariable;
randomVariable = RandomTriangular(AnnualGSIIncreasesLower, AnnualGSIIncreasesMedian, AnnualGSIIncreasesHigh);
cell.Formula = “=” + alphabet[i - 1] + “6 * (1 + " + randomVariable.ToString() + “)”;
}


for (i = 1; i <= 15; i++)
{
Cell cell = CurrentWorksheet.Cells[7, i];
//double randomVariable;
randomVariable = RandomTriangular(VacancyRateLower, VacancyRateMedian, VacancyRateHigh);
cell.Formula = “=” + randomVariable.ToString() + " * -(” + alphabet[i] + “5)”;
}

for (i = 2; i <= 15; i++)
{
Cell cell = CurrentWorksheet.Cells[10, i];
randomVariable = RandomTriangular(AnnualOperatingExpenseLower, AnnualOperatingExpenseMedian, AnnualOperatingExpenseHigh);
cell.Formula = “=” + alphabet[i - 1] + "11 * (1 + " + randomVariable.ToString() + “)”;
}

for (i = 1; i <= 15; i++)
{
Cell cell = CurrentWorksheet.Cells[39, i];
randomVariable = RandomTriangular(IncomeTaxRateLower, IncomeTaxRateMedian, IncomeTaxRateHigh);
cell.Formula = “=” + randomVariable.ToString();
}



CurrentWorksheet = workbook.Worksheets[“Sheet2”];
// B6:P6
for (i = 1; i <= 15; i++)
{
Cell cell = CurrentWorksheet.Cells[5, i];

randomVariable = RandomTriangular(AnnualAppreciationRateLower, AnnualAppreciationRateMedian, AnnualAppreciationRateHigh);
cell.Formula = “=+” + alphabet[i] + "5 * " + randomVariable.ToString();
}


CurrentWorksheet = workbook.Worksheets[“Sheet3”];

randomVariable = RandomTriangular(AnnualAppreciationRateLower, AnnualAppreciationRateMedian, AnnualAppreciationRateHigh);
CurrentWorksheet.Cells[“B14”].Formula = “=+Input!$B$12*(1+” + randomVariable.ToString() + “)^5”;

randomVariable = RandomTriangular(AnnualAppreciationRateLower, AnnualAppreciationRateMedian, AnnualAppreciationRateHigh);
CurrentWorksheet.Cells[“C14”].Formula = “=+Input!$B$12*(1+” + randomVariable.ToString() + “)^10”;

randomVariable = RandomTriangular(AnnualAppreciationRateLower, AnnualAppreciationRateMedian, AnnualAppreciationRateHigh);
CurrentWorksheet.Cells[“D14”].Formula = “=+Input!$B$12*(1+” + randomVariable.ToString() + “)^15”;

randomVariable = RandomTriangular(CapitalGainTaxRateLower, CapitalGainTaxRateMedian, CapitalGainTaxRateHigh);
CurrentWorksheet.Cells[“B20”].Formula = “=” + randomVariable.ToString();

randomVariable = RandomTriangular(CapitalGainTaxRateLower, CapitalGainTaxRateMedian, CapitalGainTaxRateHigh);
CurrentWorksheet.Cells[“C20”].Formula = “=” + randomVariable.ToString();

randomVariable = RandomTriangular(CapitalGainTaxRateLower, CapitalGainTaxRateMedian, CapitalGainTaxRateHigh);
CurrentWorksheet.Cells[“D20”].Formula = “=” + randomVariable.ToString();

CurrentWorksheet = workbook.Worksheets[“Results”];
workbook.CalculateFormula(); ;
double result = workbook.Worksheets[“Calc”].Cells[“D9”].DoubleValue;
CurrentWorksheet.Cells[k, 1].Formula = “=” + result.ToString();
}
10

One other question, if I am using

workbook.CalculateFormula(false, new CustomFunction()) continuously… should I only write workbook.CalculateFormula(false, new CustomFunction()) once and then workbook.CalculateFormula() every time after that? or must I always write workbook.CalculateFormula(false, new CustomFunction())?
11

Hi,


Thanks for the code segment.

Well, I cannot evaluate your code segment properly as I am not sure about the data and some of the variables and other objects you used in applying formulas etc.

We appreciate if you could create a sample console standalone console application (runnable) with v8.2.0 (latest), zip it and post it here to reproduce the issue on our end. Also, attach your template files (input, output etc.) if there is any with performance stats (if possible). All this helps us to evaluate your issue quickly to consequently figure it out (if found) soon.

Thank you
12

Moved here: Formula Calculation - Free Support Forum - aspose.cloud