Excel 2007 : More comprehensive Averageif function

**derpotheman** · 02-02-2011, 03:27 PM

I would like to do an averageif on cells that are greater than 0, and not filled a certain color (some shade of pink in my scenario)

obviously I know how to take care of the first filter, but what about the second, how could I do this?

edit: If this is not possible I am open to any suggestions regarding changing the pink fill color distinction to something that would enable this type of calculation.

**DonkeyOte** · 02-02-2011, 03:33 PM

Can you elaborate regards how/why they are "some shade of pink" ?

In short, is there logic in place which determines the format - if so - what is it

**arthurbr** · 02-02-2011, 03:34 PM

Is the coloring of your cells provoked by conditional formatting?

**derpotheman** · 02-02-2011, 04:01 PM

as far as I know there is no logic in place for the color of the cell, its simply selected as the fill color when you go to format cells -> fill

I'm not too sure what you mean by conditional formatting, but it sounds similar to the first question. It looks to me like the cells were manually filtered, and then manually filled through the format cell menu.

Once again I am open to any suggestions regarding changing the current method of distinction in a way that would make this calculation possible (I would still like the distinction to be easily visible though)

edit: the reason these cells are colored pink is because they represent sales that are not ordinary (as a result of a stock out, or a promotion, or something similar), so they are not to be included in our monthly sales average (what im trying to calculate). None of this reasoning is present in the formatting of the cell though, they were manually filled.

**arthurbr** · 02-03-2011, 01:17 PM

Is there any way to distinguish these cells form those of ordinary sales. Fi maybe one of the sales that adds up to this cell is called "promotion" or something

Maybe add a sample sheet with data to your post

**derpotheman** · 02-04-2011, 01:38 PM

The data is laid out rather simply, columns are months, with a final column for "average monthly sales"
and rows are items

sales that are "out of the ordinary" have gone through and been filled the color pink. The only thing that sets them apart is that they are typically much higher, or much lower, than sales in the other months, but by no fixed amount.

If there is no way to averageif(not filled, and greater than zero) then I can just mark this as solved, that is all I'm tryin to do.

The only reason I don't post an example sheet is because I'm at work and I don't think they want me to do that, but there is nothing very fancy going on like I explained in the beginning of this post.

Thanks for your help