Change sum and average if cell color change.

  • Hello,

    In range (B2: F10) have numbers. In column G we mean the sum of the rows in G2 = SUM (A2: F2) in G3 = SUM (A3: F3), etc.
    In B11 = AVERAGE (B2: B10) in C11 = AVERAGE (C2: C10) etc.
    If C4 fill it with a color I would like the amount of G4 to decrease with cell value and vice versa if you get normal color in cell the sum of column G to modify with the cell value. The same for AVERAGE of the line 11.

    For example say in C4=10 and sum in G4=100, if I change color of cell C4, sum in G4 will be 90, if I remove color of C4, G4 will be 100.
    In same way for AVERAGE in row 11


  • Re: Change sum and average if cell color change.

    Excel can't do that.
    There are inadiquate work-arounds, but the main issue is that changing a cell's color does not trigger either calculation or an event of any kind. The workbook has to be treated even more causiously than if caclulation were set to Manual.

    I'd recomend redsigning your user interface.

  • Re: Change sum and average if cell color change.

    perhaps if you had data in columns a,c,e,g, & i and narrow columns in b,d,f,h, & j. You could put an "x" in the narrow columns to indicate "do not use the data to the left".

    In that case =SUMIF(B2:J2,"<>x",A2:I2) would return the sum you want and AVERAGEIF would return the average

    A Double Click event could be written to make un/checking the columns easier.