Average if color condition is met

[BRISBANEBOB]

Is it possible to average a row only if a color condition is met (example on the attached spreadsheet)?

[Paul]

Hi again, Bob.

There's no built-in function in Excel that will do this, but a UDF will do the trick. For example, place the following function into a standard Module in the VB Editor:

Please Login or Register  to view this content.

Then use the following formula in any cell:
=AvgColors(C8:G8)

The code loops through the cells in the range you specify, and if the cell in row 7 of any column is green (colorindex=50 using your example spreadsheet), then the code adds that cell value to the sum, and increases the count of cells by 1. At the end, as long as more than one cell had a green background in row 7, the average is displayed (otherwise zero is returned).

[shg]

You might instead use a formula that averages based on the criteria on which the cells are colored.

[BRISBANEBOB]

Thanks (again) for that, Paul

SMG, can you expand on what you mean? Soryy, I'm a newbie to all of this.

Thanks

[BRISBANEBOB]

Hi Paul

I'm getting a #NAME? error which i think means the spreadsheet can't find the declaration (?). If I right click on the sheet tab I can see the code I pasted into VB

Where am I going wrong?

[shg]

You might instead use a formula that averages based on the criteria on which the cells are colored.

can you expand on what you mean?

Assuming the cells are not colored at random, there is some formula that can be entered in, say, row 6, that returns TRUE if the column is to be included in the average.

Then you can use the results in that row to both color the cells (using conditional formatting) and to control the averaging formula; the formula in I8 and copied down could be

=SUMIF(C$6:G$6, TRUE, C8:G8) / COUNTIF(C$6:G$6, TRUE)

Philosophically, the cells are colored for a reason; that same reason (and not the symptom of color) should be used for the formula. And if you decide you prefer a different highlight color, or to highlight using boldface print, you only have to change one thing (the CF) and not two.

[Paul]

Bob, make sure the code is in a standard module, not a worksheet module (in the VB Editor, click Insert -> Module).

Shg's recommendation may also work if you can incorporate it into your workbook.