Excel - Calculate range by background color

[jminn0311]

I have a spreadsheet that I need to calculate the averages for cells that have no specific pattern. I manually change the background of the cells that need to be averaged and was wondering if there was an easy way to do this.

Something like =averageif background=red or =averageif background=green.

Maybe someone has a snippet of vb code that would make this possible also.

Many thanks
jtm

 

[xlbo]

I'll start by saying that this is a very bad idea - there is NO natural functionality to do this in excel. You are FAR better off using a code letter or number to mark the cells that need averaging - you then have a number of options.

If you are dead set on using colours, you will need a User Defined Function utilising VBA. That being the case, please have a look at the FAQs and search the VBA forum (Forum707). I know that there are several functions that have been written in the past to deal with this.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question