Search code examples
excelexcel-formulaiif-function

Is it possible to add/subtract in one cell, and have it subtract/add in another cell that already has a value?

Is it possible to add/subtract in one cell, and have it subtract/add in another cell that already has a value? I am thinking it may be a if function but I can not wrap my head around how I would write out the formula.

Solution

  • Let's say you have 2 columns B and C that already contain data. And if you Add a number to B you want that number to be subtracted from C.

    My recommendation is to write a macro that will work as follows:

    First the user selects the two columns and then runs the macro

    For each row
    Cell c = getCell("C" + row);
    double cval = c.Value;        
    c.type = FORMULA;
    c.Formula = "=" + (cval + getCell("B" + row).Value) + "-B"+row;
    c.Recalculate()

    Example:

    Original:

          A     B     C
1    Gas    5    10
2    Air    8    12

    Replace with:

         A     B        C
1   Gas    5    =15-B1
2   Air    8    =20-B2

    so you only change B, and the value of C is automatically calculated.