SOLVED

if function

New Contributor

I am trying to create an if statement where if a cell is a certain colour, I would like the average of all of those cells of that colour. 

 

I have created a module for interior colour - 

Function InteriorColor(CellColor As Range)
Application.Volatile
InteriorColor = CellColor.Interior.ColorIndex
End Function

 

Then in a cell have =interiorcolor(U5)

What is the next formula I should use so that if any cells have this colour it will average them?

 

Thanks

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@Leesa150 

Let's say that the range with the numbers and colors is T5:T100, and that the InteriorColor formulas are in U5:U100.

The average value of the cells in T5:T100 with the same color as T5 is

 

=AVERAGEIF(U5:U100,U5,T5:T100)

@Hans Vogelaar Awesome thanks - took a few times reading what you wrote to work it out but I've got it now. Thanks for your help