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

Thanks.

• 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.

Thanks for replay.

I saw this things in an excel file, but I have not had access to that file and see the code. So anything is possible but I do not know if it's a UDF function or VBA code.

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

You could assign code to a button, but however you did it would have to run it manually as needed.

• 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.

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

Thank you mikerikson

Unfortunately I can use only one helper column. Thanks for the solution time.