Search code examples
javascriptexcelexcel-formulapseudocodearray-formulas

Need help converting a Multi-Cell Excel formula to basic pseudo code


I don't think the whole spreadsheet is relevant here (Hope I am not wrong) but essentially I am working with some financial figures and need to work out a "Cumulative Cost". The spreadsheet is correct, but I don't understand the maths of the formula, so I hope somebody can break it down into BODMAS or pseudo code or something (or even Java which it will ultimately be.)

{=(PRODUCT($D$4:D7/100+1)-1)*100}

{=(PRODUCT($D$4:D8/100+1)-1)*100}

{=(PRODUCT($D$4:D9/100+1)-1)*100}

etc..

I think I only needed to supply one formula, but just giving a few more for context.

So the formulas above are found in column E: Screenshot of Table

Thanks!


Solution

  • The part of the formula, /100+1, converts from a percentage change. The part, -1)*100, converts to a percentage change. PRODUCT() multiplies the numbers togther.

    double d[12], e[12]; // input and output arrays, respectively
    double p = 1.0; // to accumulate the product
    
    for (i = 0; i < 12; i++)
    {
        p = p * ((d[i] / 100) + 1);  // convert from percentage and multiply
        e[i] = (p - 1) * 100;        // convert to percentage
    }
    

    Additional ( and ) and spaces added for clarity.