if statement on a range

[rcr1991]

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?

thanks in advance,

rob

 

[Tazguy37]

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

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]

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?

thanks in advance,

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

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]

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]

ADVERTISEMENT

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]

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]

ADVERTISEMENT

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]

Here is a small example.

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]

norie,

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

thanks!

rob