# if statement on a range

#### rcr1991

##### New Member
i have a named range:

Set myrng = ActiveSheet.Range(Cells(x_rows, 2), Cells(y_rows, 2))

i want to get an average of the values in that range of all the cells where the cell is not colored red

i'm stuck.

concept is : average (myrng, cells where interior.color <>3)

suggestions?

rob

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### Tazguy37

##### MrExcel MVP
How about something like this (in a standard module):

Code:
``````Sub getRedAvg()
'blank cells count as zeroes as written
Dim cell As Range
Dim myAvg As Variant
Dim myCount As Double
Dim mySum As Double

For Each cell In Selection
If IsNumeric(cell.Value) Then
If cell.Font.ColorIndex <> 3 Then 'red
mySum = mySum + cell.Value
myCount = myCount + 1
End If
End If
Next cell

If myCount = 0 Then
myAvg = "N/A"
Else
myAvg = mySum / myCount
End If

MsgBox myAvg

End Sub``````

Hope that helps!

#### Yogi Anand

##### MrExcel MVP
rcr1991 said:
i have a named range:

Set myrng = ActiveSheet.Range(Cells(x_rows, 2), Cells(y_rows, 2))

i want to get an average of the values in that range of all the cells where the cell is not colored red

i'm stuck.

concept is : average (myrng, cells where interior.color <>3)

suggestions?

rob
Hi rob:

Formula wise, If you used Conditional Formatting, then apply the same condition to get the desired average.

Since you asked for a VBA solution, the following is a brute force method using rows 2, and 11 as the starting and ending rows of your range
Code:
``````Sub y_4()
intColor = 3
myCount = 0
mySum = 0
Set myrange = ActiveSheet.Range(Cells(2, 2), Cells(11, 2))
For Each cell In myrange
If cell.Interior.Color <> intColor Then
mySum = cell + mySum
myCount = myCount + 1
End If
Next cell
myAverage = mySum / myCount
MsgBox myAverage
End Sub``````

#### rcr1991

##### New Member
thank you v. much for suggestions.

however, i would like to use average(range) and stdev(range) or sqrt(range) etc. on the range of cells not including values of red cells.

while the average calculations will work, the stdev, sqrt, etc. solutions are not so obvious.

should i make a new range first that only includes non-red cells?

or other ideas?

thanks,

rob

#### Tazguy37

##### MrExcel MVP
rcr1991 said:
while the average calculations will work, the stdev, sqrt, etc. solutions are not so obvious.

You could use Application.WorksheetFunction.StDev(Selection) to get the standard deviation of the selected range.

How would you evaluate the square root of a range?

#### rcr1991

##### New Member
tazguy,

yes, dumb example re: sqrt. just trying to say that i need to perform something besides simple math on the range, once i select the NON_RED cells in that range. the other helpful fellows solved how to identify the cells that are not red, and sum them up.

but i need to exclude those darn red colored cells before using:

Application.WorksheetFunction.StDev(Selection)

can you propose a way to make the cells identified in this code a selection that i can use in your example?

For Each cell In myrange
If cell.Interior.Color <> intColor Then
.......
End If
Next cell

tia,

Rob

#### rcr1991

##### New Member
still stuck, would new array answer problem?

all of these suggestions are great for getting the average, but if i want to have a new group of cells thqat can be addressed by functions like stdev, then i need some way of groups these cells, not looping through them one at a time.

would creating a new array based on red/not red interior cell color work?

suggestions?

#### Norie

##### Well-known Member
Here is a small example.
Code:
``````Sub test()
Dim rng1 As Range
Dim rng2 As Range
Dim c As Range

Set rng1 = Range("A1:A3")

For Each c In rng1.Cells
If c.Interior.ColorIndex = 3 Then
If rng2 Is Nothing Then
Set rng2 = c
Else
Set rng2 = Union(rng2, c)
End If
End If
Next c

MsgBox Application.WorksheetFunction.StDev(rng2)
End Sub``````

#### rcr1991

##### New Member
norie,

thanks, i see what you're doing. will try right away. the 'union' command may be just what i was missing.

thanks!

rob

Replies
5
Views
127
Replies
9
Views
454
Replies
1
Views
875
Replies
2
Views
239
Replies
11
Views
673

1,195,650
Messages
6,010,915
Members
441,572
Latest member
keobongda8812

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back